Home > SQLServer, SQLServer 2012 > SQL Server – "Denali" – End of Month using EOMONTH()

SQL Server – "Denali" – End of Month using EOMONTH()

SQL Server Denali CTP3 introduces a new date/time function EOMONTH(). EOMONTH() returns the last day of the month for the given date. It also accepts an optional argument which is an integer expression specifying number of months to add/remove from specified date.

Syntax:

       EOMONTH( start_date [, months_to_add] )

It’s similar to Excel’s EOMONTH() function to calculate last day of the month.

It can be used as below:

SELECT DATEADD(s, 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))

       AS 'Last day of Month', — Earlier Versions

       EOMONTH(GETDATE())

       AS 'Last day of Month',

       DATEADD(s, 1, DATEADD(day, 1, EOMONTH(GETDATE())))

       AS 'Last day of Month'

       — Time part 23:59:59

Result Set:

Last day of Month       Last day of Month       Last day of Month

———————– ———————– ———————–

2011-07-31 23:59:59.000 2011-07-31 00:00:00.000 2011-07-31 23:59:59.000

 

(1 row(s) affected)

You can use DATEADD() and EOMONTH() to calculate first day of the month as follows:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

       AS 'First day of Month', — Earlier Versions

       DATEADD(day, 1, EOMONTH(GETDATE(), 1))

       AS 'First day of Month'

Result Set:

First day of Month      First day of Month

———————– ———————–

2011-07-01 00:00:00.000 2011-07-01 00:00:00.000

 

(1 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
Categories: SQLServer, SQLServer 2012
  1. July 26, 2011 at 10:33 pm

    So, just curious, what do you think is the advantage of this function? Save a few keystrokes? How often are you interested in the last day of a month at midnight? Seems like not much work is saved if you’re doing something like adding a day and then subtracting a second to get the *real* end of the month (which isn’t really the end of the month – be very careful with those assumptions as they can round or miss data depending on the data type). Personally I think BOMONTH would have been a lot more valuable, since almost all reporting queries I’m going to run make more sense when issued as >= [start of Jan] AND < [start of Feb].

  2. July 27, 2011 at 9:14 am

    You are right,
    it saves a few keystrokes. I think EOMONTH() is more useful/easier (in terms of keystrokes) to get first day of the month than last day of month (with 23:59:59)..

  3. GilM
    July 28, 2011 at 12:00 am

    For Beginning of Month, I think it would be much clearer to do it like this:

    DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1)

    Or DATETIMEFROMPARTS if that’s what you want.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: