Did you ever faced a situation when you need quickly calculate some boundaries of the Date value like First Day of the month, Last Day of the month, etc… in T-SQL?
Colegue of mine asked me this question… WOW! What do you know…
It is actually very easy if we think about it for a second…
So lets fun begin…
--First Day of the Current Month:
select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
-- Last Day of the Current Month:
select DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
-- Last Day of Prior Month:
select DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
-- First Day of the Current Year:
select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
-- Last Day of the Current Year:
select DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0))
-- Last Day of Prior Year:
select DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
And some more…
-- First Day of the Quarter:
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
-- Monday of the Current Week:
select DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
-- Midnight for the Current Day:
select DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
-- First Monday of the Month:
select DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, 6 - DATEPART(day, GETDATE()), GETDATE())), 0)
-- Last 2 full months
select DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0))
