Find the 28th day of every month
Is there a way to find the 28th day of the month based upon the date provided in a query? I am trying to calculate the Datediff between the date provided in the query and the 28th of the current month; to see what the gap is between these dates. If the date is passed the 28th (29, 30,31), move on to the next months 28th date.
Best Answer
Try this....
declare @YourTable table (somedate datetime) insert into @YourTable (somedate) select '1 Jan 2018' insert into @YourTable (somedate) select '27 Jan 2018' insert into @YourTable (somedate) select '28 Jan 2018' insert into @YourTable (somedate) select '29 Jan 2018' insert into @YourTable (somedate) select '31 Jan 2018' insert into @YourTable (somedate) select '31 Dec 2018' select somedate, case when datepart(day, somedate) > 28 then dateadd(day, 28, eomonth(somedate)) else dateadd(day, 27, dateadd(day, 1, eomonth(somedate,-1))) end from @YourTable somedate ----------------------- ---------- 2018-01-01 00:00:00.000 2018-01-28 2018-01-27 00:00:00.000 2018-01-28 2018-01-28 00:00:00.000 2018-01-28 2018-01-29 00:00:00.000 2018-02-28 2018-01-31 00:00:00.000 2018-02-28 2018-12-31 00:00:00.000 2019-01-28 (6 rows affected)
