this date formula should return a specific date based on day 25th of each month
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
- some of your entered dates are not dates at all
- what datatype if the date parameter? date or varchar?
- your first sample is 2021-05-27 but you say expected is 25-04-2021
- Please fix and respond back
Select case when DATEPART(month, @date) > 25 then Else End
What if the date is 25th?
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
- 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
thank you for the script, i'll test it in my case and feed back
What planet is this date from 26-25-2021
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
thank you ScottPletcher , smart formula, it works perfect.
Scott's solution is also 69% faster.
Nice, simple formula, Scott.