Date formula

Transact-SQL
1

Hi everyone,
this date formula should return a specific date based on day 25th of each month
as below:
select dateadd(month,datediff(month,0,dateadd(m,-1,'2021-05-23')),24)
the result will be: 25-04-2021
I need the result to be 25th of the same month once entered any date after 25 and previous month once entered date before 25
below are many examples for entered & result dates needed
entered ===> result
24-05-2021 ===>25-04-2021
25-05-2021===>25-04-2021
26-25-2021===>25-05-2021
01-06-2021===>25-05-2021
23-06-2021===>25-05-2021
any advise?

2
  1. some of your entered dates are not dates at all
  2. what datatype if the date parameter? date or varchar?
  3. your first sample is 2021-05-27 but you say expected is 25-04-2021
  4. Please fix and respond back
Select case 
when DATEPART(month, @date) > 25 then 
Else
End

What if the date is 25th?

3

without knowing your data type

declare @tanned table(choco date)

insert into @tanned
select convert(date, choco, 105)
from (
	select '24-05-2021' as choco union all
	select '25-05-2021' union all
	select '26-05-2021' union all
	select '01-06-2021' union all
	select '23-06-2021' 
) a

;with _25th
as
(
--go to the 1st of the month and add 25
select dateadd(d,(DAY(choco)*-1) + 25, choco) as _25thThisMonth, 
       choco
from @tanned
)
select choco,
		case 
		-- > 25 same month 25th
		when DATEPART(dd, choco) > 25 then _25thThisMonth
		-- <= 25 previous month 25th
		Else dateadd(m,-1,_25thThisMonth)
		End
From _25th

image

1 Like
4

hi yosiasz

  • the entered data type will be date as I'll replace the sample date with GETDATE()
  • the first sample modified as it has a mistake
  • if the date is 25th it should return the previous month
5

thank you :slight_smile: for the script, i'll test it in my case and feed back

6

What planet is this date from 26-25-2021

7

I prefer to avoid using CASE when possible:

SET DATEFORMAT DMY

;WITH cte_test_dates AS (
    SELECT * 
    FROM ( VALUES
        ((CAST('24-05-2021' AS date))),
        ('25-05-2021'), 
        ('26-05-2021'),
        ('01-06-2021'), 
        ('23-06-2021') ) AS dates(date)
)
SELECT 
    date AS original_date,
    /* THIS IS THE FORMULA -- the rest of the code is just presentation */
    CAST(DATEADD(DAY, 24, DATEADD(MONTH, DATEDIFF(MONTH, 0, 
        DATEADD(DAY, -25, date)), 0)) AS date) AS calculated_date
FROM cte_test_dates
3 Likes
8

thank you ScottPletcher :slight_smile: , smart formula, it works perfect.

9

Scott's solution is also 69% faster.

1 Like
10

Nice, simple formula, Scott.