Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

T-SQL Function: EOMONTH

We have all been in a situation where we had to get the last day of the month with T-SQL. The world would just be so much easier with 30 days in every month, which is not the case. To calculate if the last day is 31, 30, 28 or maybe even 29 is not that easy. There are many solutions to this problem out there, here is the one that i normally use:

DECLARE @myDate Datetime = '2012-02-12'
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @myDate) +1, 0))

This code finds the difference in months, adds one month (this gives us the first day of the next month) and then substracts one day. And the result:

blog

The last day of february next year is the 29th.

With the introduction of SQL Server Denali, we get a new function called EOMONTH. The EOMONTH takes two parameters the first one being the date and the next optional one being the number of months to add to the start date.

EOMONTH (start_date [, month_to_add]

Let’s have a look at how my code now will look:

DECLARE @myDate Datetime = '2012-02-12'
SELECT EOMONTH (@myDate) AS Result;

EOMONTH returns a type of start_date or the datatype datetime2.

Now I started searching BOL like crazy to find the BOMONTH function, but no no that is not to be found, yet? Maybe somebody should create a connect issue with Microsoft on that. But anyway by using the EOMONTH function it is also pretty easy to get the first day of the month, here is the code:

DECLARE @myDate Datetime = '2012-02-12'
SELECT DATEADD(day, 1, EOMONTH(@myDate, -1)) as FirstDayOfMonth

This is not the biggest new feature in SQL-Server Denali, but anyway it saves me lines of code, so I like it.

@ms1333

Comments

Posted by Edafe Onerhime on 16 September 2011

I really like this new function, it's one of the little changes that make life a lot easier.

Posted by charles.southey on 16 September 2011

Functions like this are a long-awaited addition to the T-SQL language and very welcome. However if you can't wait for Denali or need even richer date-based functions (including working days calculations similar to those found in Excel) you could check out our SQLUtilities library - www.totallysql.com/products/sqlutilities.

Posted by dvorix on 16 September 2011

Nice function.

I usually using table with generated dates and using join or subselect.

Not so simple, but have another benefits (like holidays atc.)

SELECT '2012-02-12'

      ,(select top 1 d.date

        from dates d where year(d.date) = year(t.date) and month(d.date) = month(t.date)

        order by 1 desc)

from table1 t

Posted by jj on 16 September 2011

I would upgrade just for this function... :-)

Posted by yuping.su on 16 September 2011

Another way to do it:

EOMONTH:

DECLARE @myDate Datetime = '2012-02-12'

select dateadd(month, 1,@myDate-DAY(@myDate))

BOMONTH:

DECLARE @myDate Datetime = '2012-02-12'

select @myDate-DAY(@myDate) +1

Posted by David Walker on 16 September 2011

Small additions like this can make the programmer's job easier.  I wish there was a function that would return the smaller or larger of the arguments passed to it, as in

Set @NewValue = MinValue(@Value1, @Value2)

Something like that could be useful when updating tables too.  The Min and Max aggregate functions aren't always exactly what I need.  

Obviously what I want can be done using the Case statement, but it's a little tedious that way.

Posted by jjezequel on 16 September 2011

There is a small mistake on the first code(DECLARE @myDate Datetime = '2012-02-12'

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @myDate) +1, 0))), the value returned is '2012-12-31'.

Posted by dlchase on 16 September 2011

How can you pass 2 values to EOMONTH function when it only accepts 1 in getting first day of month? Shouldn't it read

SELECT DATEADD(day, 1, EOMONTH(@myDate), -1) as FirstDayOfMonth

Posted by Ashley McLellan on 20 September 2011

I like using the below to get the time to 23:59:59:000 as well

DECLARE @MyDate as datetime = getdate()

select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@MyDate)+1,0))

Leave a Comment

Please register or log in to leave a comment.