In Denali we have a new date-related function called EOMONTH() - it comes straight to us from VBA so many Excel and Access users will know exactly what it does. For those that don't, it returns the last day of the month for the date passed in, returning the same data type as the input. There is an optional argument, month_to_add (probably should be months_to_add), that allows you to specify how many months should be added - this is the same as saying DATEADD() before passing @date to the function. A quick example:
DECLARE
@date DATE = '20110501';
SELECT
CurrentMonth = EOMONTH(@date),
FollowingMonth = EOMONTH(@date, 1),
PreviousMonth = EOMONTH(@date, -1);
|
Results:
CurrentMonth FollowingMonth PreviousMonth
2011-05-31 2011-06-30 2011-04-30
|
The return type is based on the type passed in - if you declare @date as DATETIME2(7), that's what you get back - even though there is never going to be any time component (so arguably the output could always be DATE).
But even more generally, I think this function is kind of pointless. Yes, I absolutely agree that it is now less code to calculate the last day of the month. But how often are you getting that?
Date Range Queries
If I'm performing a date range query, for example, I want the whole month. It is not very useful to say:
WHERE OrderDate BETWEEN '20110201' AND EOMONTH('20110201');
|
This translates to:
WHERE OrderDate BETWEEN '20110201' AND '20110228';
|
Which of course will miss any orders placed on the 28th not placed at exactly midnight (a lot of folks miss that about BETWEEN). In order to capture the whole month, I still need to convert it to an open-ended date range, e.g.:
WHERE OrderDate >= '20110201' AND OrderDate < DATEADD(DAY, 1, EOMONTH('20110228'));
|
The only time the BETWEEN syntax may make sense is when the base data type is DATE. But still, I'd be nervous about that because, if the data type changes to be more precise, these queries won't break, they will just start producing inaccurate results.
Number of Days
Maybe a more practical use is to get the number of days in the month of any given date, for example is February 2012 a leap year?
DECLARE @days TINYINT = DATEPART(DAY, EOMONTH('20120201'));
|
But I can also do that using:
DECLARE @days TINYINT = DATEPART(DAY, DATEADD(DAY, -1, '20120301'));
|
Sure, the new approach is a few characters lighter, but I could have shaved just as many characters by shortening my variable name and just using INT as the type. It turns out 2012 is a leap year, by the way.
Persisted Computed Columns
Another downside to the new date/time functions like EOMONTH is that they can't be used in a persisted computed column, because they are not deterministic:
CREATE TABLE dbo.eomonth
(
d DATETIME,
e AS CONVERT(DATE, EOMONTH(d)) PERSISTED
);
|
Result:
|
Msg 4936, Level 16, State 1, Line 1
Computed column 'e' in table 'eomonth' cannot be persisted because the column is non-deterministic.
|
In those cases you're going to have to use old methods:
CREATE TABLE dbo.eomonth
(
d DATETIME,
e AS CONVERT(DATE, DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(d), d))) PERSISTED
);
|
Summary
I think a function to find the beginning of the month would have been
*much* more useful, though I probably would spell something out rather
than use old abbreviations for consistency - BOMONTH sounds like a
30-day celebration of giving up deodorant. Looks like other similar functions won't be added in this version.
I haven't had a whole lot of bad things to say about Denali (early SSMS snafus notwithstanding), but in this case it seems to be quite the case of checking something off someone's checklist. I'll be happy to be proven wrong though, and I'd love to hear stories of how you are going to use this function to save time or improve performance - please enlighten me!