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))

4 Comments

Karen · Apr 5, 2007 at 12:06

I need to add 18 months to the below; for example, 3/1/2006 returns 9/1/2007; 5/1/2007 returns 11/1/2008.

dateadd(ms, -3, DATEADD(mm, DATEDIFF(m, 0, terminationdate) + 1, 0))

Sir. Hacksalot · Sep 11, 2012 at 16:29

Can someone please help with setting a date for (Last day of prior month, in prior year)
example: if today is 09/11/2012 the results would be 08/31/2011.

Roy · Oct 16, 2012 at 08:53

I need a querry that can return an end date from start date and number of months

    VIP · Nov 26, 2012 at 12:04

    select distinct
    dateadd(mm,DATEDIFF(mm, 0, GETDATE()),0)
    , dateadd(ss,-1,dateadd(mm,DATEDIFF(mm, 0, GETDATE())-12,0) ),
    datediff(mm,dateadd(mm,DATEDIFF(mm, 0, GETDATE()),0), dateadd(ss,-1,dateadd(mm,DATEDIFF(mm, 0, GETDATE())-12,0) ))

Leave a Reply