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:
Always nice to try
Yup that works too!
Now lets try somethiing with the offset.
SELECT EOMONTH('2013-02-12', -10000)
Thats a lot of years back..
Now let's try to crash the EOMONTH() function.
SELECT EOMONTH('2013-02-12', -100000)
Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime2' column caused an overflow.
Let't try some implicit conversion of the function
Let's try an impossible implicit conversion
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Let's try a another impossible implicit conversion
Msg 8116, Level 16, State 1, Line 1
Argument data type int is invalid for argument 1 of eomonth function.
SELECT BOMONTH = EOMONTH(getDate(), -1) + 1
FROM dbo.FactResellerSales RS
INNER JOIN DimDate D ON RS.OrderDateKey = D.DateKey
WHERE D.FullDateAlternateKey Between CAST('20040501' AS DATETIME) AND EOMONTH('20040501')
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....
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:
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.