SQL Server v.Next (Denali) : Will you use EOMONTH()?
In SQL Server 2012, we have a new date-related built-in function called
EOMONTH – it comes straight to us from VBA, so many Excel and Access users will know exactly what it does. For those who 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), so you can specify how many months should be added – this is the same as saying
DATEADD(MONTH, month_to_add, @date) 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);
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
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
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? My mindset is always that I don't need to know the last day of a period unless it is for some label at the presentation layer; when I need that, I know I can always get the last day of this period by subtracting one day from the first day of the next period.
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';
To be more explicit:
WHERE OrderDate BETWEEN '20110201 00:00:00.000' AND '20110228 00:00:00.000';
When written that way, it's clear the query will miss any orders placed on the 28th any time after 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'));
In which case, I may as well just say:
WHERE OrderDate >= '20110201' AND OrderDate < '20110301';
The only time the
BETWEEN syntax may make sense is when the base data type is (and always will be)
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.
I wrote a whole thing about how
BETWEEN is the devil.
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 either of the following:
DECLARE @day1 tinyint = DATEPART(DAY, DATEADD(DAY, -1, '20120301')); DECLARE @day2 tinyint = DATEDIFF(DAY, '20120201', '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/or just using
int as the type. It turns out 2012 is a leap year, by the way.
Persisted Computed Columns
It is fixed in more modern versions but, at the time,
EOMONTH couldn't be used in a persisted computed column definition, because the result was deemed to not be deterministic:
CREATE TABLE dbo.eomonth ( d datetime, e AS CONVERT(date, EOMONTH(d)) PERSISTED );
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 );
That is really ugly, but this might also work (don't have an old enough version to test):
e AS DATEFROMPARTS(YEAR(d),MONTH(d),DAY(d)) PERSISTED
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!