SQL date operations: First day, Last day of the month…

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))
This entry was posted in Fun stuff with SQL Server and tagged , , , . Bookmark the permalink.

4 Responses to SQL date operations: First day, Last day of the month…

  1. Karen says:

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

  2. Sir. Hacksalot says:

    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.

  3. Roy says:

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

    • VIP says:

      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

  • About

    From being a junior developer all the way to Development Manager position, I was always interested in new technologies. Passionate speaker, IT junky, developer, architect, team lead, and development manager - many hats, one goal - making software better and closer to people’s needs. For the most part I am using my blog as a scratch pad, writing small articles on things which I came across, was asked about more then once, and which would otherwise require additional research again and again.

    View Serguei Dosyukov's profile on LinkedIn
  • Testimonials

    First I’m new to G3. I was wanting to append my forum with a gallery and after doing a lot...

    Setishock
    http://www.animeappeal.com

    I’m a huge fan of this theme. I’ve got more than 90,000 pictures in my gallery3 running on a Ubuntu...

    Jklobo

    I've been using this [theme] since the start. It is by far the most attractive, feature packed and stable of...

    Rhyull

    more...

  • Categories