Home > Denali, TSQL > SQL Server Denali – EOMONTH()

SQL Server Denali – EOMONTH()

SQL Server Denali introduces 7 new date and time built-in functions. One of these functions is EOMONTH(). EOMONTH() returns the date value that represents the last day of the month for given date.

Syntax: EOMONTH( start_date [, months_to_add] )

Pre- Denali, to calculate the date value that represents the last day of the month we used:

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))
/*
-----------------------
2011-08-31 00:00:00.000
*/

In Denali, we can use EOMONTH function.

SELECT EOMONTH (GETDATE())LastDay1,
EOMONTH('2011-09-05') AS LastDay2
/*
LastDay1                LastDay2
----------------------- ----------------------
2011-08-31 00:00:00.000 2011-09-30 00:00:00.00
*/

If we specify “months_to_add” argument, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.

SELECT EOMONTH(GETDATE(),-1) AS LastDayOfPreviousMonth,
EOMONTH(GETDATE()) AS LastDayOfMonth,
EOMONTH (GETDATE(),1)LastDayOfNextMonth
/*
LastDayOfPreviousMonth  LastDayOfMonth         LastDayOfNextMonth
----------------------- ----------------------- -----------------------
2011-07-31 00:00:00.000 2011-08-31 00:00:00.000 2011-09-30 00:00:00.000
*/

It is strange that SQL Server team hasn’t added a function for beginning of the month.  For me, BOMONTH would be more valuable because almost all reporting queries I’m writing make more sense with first date of month.

To calculate the first day of the month for given date.

-- Pre-Denali
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
-- With EOMONTH function
SELECT DATEADD(DAY,1,EOMONTH (GETDATE(),-1))
-- or with DATETIMEFROMPARTS - new Denali datetime function
SELECT DATETIMEFROMPARTS (YEAR(GETDATE()),MONTH(GETDATE()),1,0,0,0,0)
Advertisements
Categories: Denali, TSQL
  1. 02/09/2011 at 6:19 PM

    I liked your article is an interesting technology
    thanks to google I found you

  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: