Denali : 10 situations with the EOMONTH() function.

Hi,

In Denali is a new function is available: EOMONTH() and it returns the last day of the month of specified date, with an optional offset. More information can be found on technet. Return type is the type of start_date or datetime2(7). EOMONTH() is the same name as used in Excel.

So i tried some things with this function. Below you can see the results:


Situation 1

SELECT EOMONTH(getdate())

Returning:

Always nice to try

SELECT EOMONTH('2012-02-12') 

Returning:
Situation 2

Yup that works too!


Situation 3
Now lets try somethiing with the offset.

SELECT EOMONTH('2013-02-12', -10000)

Returning:

Thats a lot of years back..


Situation 4
Now let's try to crash the EOMONTH() function.

SELECT EOMONTH('2013-02-12', -100000)

Returning:

Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime2' column caused an overflow.


Situation 5
Let't try some implicit conversion of the function

  SELECT EOMONTH('20110721')

Returning

Situation 6
Let's try an  impossible implicit conversion

SELECT EOMONTH('20110750')

Returning

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Situation 7
Let's try a another  impossible implicit conversion
SELECT  EOMONTH(20110750)
 
Returning

Msg 8116, Level 16, State 1, Line 1
Argument data type int is invalid for argument 1 of eomonth function.


 
Situation 8
BOMonth()?

SELECT BOMONTH = EOMONTH(getDate(), -1) + 1
 
Returning

Situation 9
SELECT *
FROM dbo.FactResellerSales RS
INNER JOIN DimDate D ON RS.OrderDateKey = D.DateKey
WHERE D.FullDateAlternateKey Between CAST('20040501' AS DATETIME) AND EOMONTH('20040501')

Returning







Situation 10
Hmm the last one... Can't think of a new situation anymore. I'm out of inspiration! I suggest that you think about the tenth situation and let me know what you've came up with....

Conclusion
EOMONTH() is an interesting function but as other bloggers suggests, why is the only new Date function that is released. There are so many datefunctions to think about...

Other interesting functions to discover are:

•     IIF
•     CHOOSE
•     CONCAT
•     DATEFROMPARTS
•     TIMEFROMPARTS
•     DATETIME2FROMPARTS
•     DATETIMEFROMPARTS
•     SMALLDATETIMEFROMPARTS
•     TRY_CONVERT
•     FORMAT

This is not a complete list.
Very interesting link is on beyond relational : http://beyondrelational.com/whatisnew/sqlserver/denali/ where you can find all the features released in Denali.

Greetz,
Hennie