question

wolfeste avatar image
wolfeste asked

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.

sqlserver-2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

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)

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That worked perfectly. Thank you

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.