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 @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? 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 );
Result:
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
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!
I completely agree with your point. I was simply pointing out that I could uses in my domain where it will save me some dateadd(dateadd(… steps. The potential for misuse is certainly a concern.
Kenneth, I think we're talking about two different things. You're talking about display, I'm talking about the underlying code. If you want to use EOMONTH() to group or to show the last day of a period in a report or some other presentation (such as a dimension for a cube), by all means, that is absolutely fine with me. What I am against is using EOMONTH() for range queries of any kind – so you shouldn't take that output and jam it into a BETWEEN or >= / < query unless you've further adjusted it to take into account the data that it misses. Since it is time-stamped at midnight, it isn't really the end of the month (except in the case where the underlying data type is DATE), is it?
Aaron, I cannot say their is any 'value' in using one date over the other. In most cases (for me) the end of period value has more meaning as that is what the data truly represents.
This usually ends up in a cube where the user only ever sees the month and year anyway. In that scenario, I see no benefit to one method vs the other.
Do you have a different view?
What I'd rather do in your first example is create my own functions called ReportingPeriodStart / ReportingPeriodEnd. Now your users don't have to learn SQL, they just have to learn which functions to use. Because if they can't do date math are they really going to understand the differences between the various date/time data types, the implications of your constraints, and the nuances of EOMONTH?
The problem with the second example is when you rely on the behavior of one function because of some other constraints that happen to exist in one specific scenario, you then have this habit of trusting EOMONTH, and using it in places where those constraints or no longer true (or later get removed). In Denali (in fact 2008+) if you're using DATETIME with no time component, you're using the wrong data type anyway. I would *much* rather program consistently and not have to worry about when the underlying constraints or the data type itself changes…
Well the first use case that comes to mind is for my users who do stuff in Excel, they have a decent understanding of SQL but the date math stuff from time to time causes issues so an explicit EOMONTH function can be helpful there.
Also I have seen cases where datetime is used but the time is always midnight (even enforced by code) so the midnight problem isn't really a problem.
Adam it is still subject to the same potential complications, e.g. demonstrating again the case where the same input leads to different output:
SET LANGUAGE BRITISH;
DECLARE @d DATE = '2/12/2011', @dt2 DATETIME2 = '2/12/2011';
SELECT EOMONTH(@d), EOMONTH(@dt2);
GO
SET LANGUAGE ENGLISH;
DECLARE @d DATE = '2/12/2011', @dt2 DATETIME2 = '2/12/2011';
SELECT EOMONTH(@d), EOMONTH(@dt2);
Now when applied against a column, which is long after any language- or regional-based decisions have been made, it *should* be deterministic. I think we should have more control over determinism and also over what formats are allowed to be implicitly converted to date/time. I'd lvoe to have a switch where any format other than ISO8601 would be rejected, regardless of language or regional settings. Folks who use m/d/y or d/m/y for literals are just asking for trouble. IMHO.
EOMONTH is never deterministic as far as I can tell (I am in front of a computer with Denali installed 🙂
This is a bit odd, because given a deterministic input date – and that usually means using CONVERT with a deterministic style for string inputs – it's tough to see how the 'last day of the month' could ever vary
Perhaps EOMONTH uses a non-deterministic function like DATEPART internally? Perhaps it was a last-minute addition, and a bit quick 'n' dirty? Who knows.
It is a surprising addition to the language, and not one I see myself using much, but it does have one advantage over the 'old method' shown at the end of this post:
CONVERT(DATE, DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(d), d)))
…in that EOMONTH does not cause an overflow error when a date in December 9999 is involved 🙂
Should be deterministic with a DATE[TIME][2] input parameter, I'd guess? (Not in front of a computer w/ Denali so I can't test.)
I will probably migrate over to it eventually but I'm so used to the usual date arithmetic that this feature is rather yawn-worthy to say the least.
I suspect it's because you can pass in a string literal, in which case the output will depend on your regional settings. Try the following:
SET LANGUAGE BRITISH;
SELECT EOMONTH('2/12/2011');
SET LANGUAGE ENGLISH;
SELECT EOMONTH('2/12/2011')
I'm just curious as to how EOMONTH is non-deterministic. For a given input, it would seem to me that it would always generate the same result. Unless I'm missing something, that's the definition of determinism.
Kenneth, can you explain what value you gain from using the last day of the month as the key, instead of the first day of the month?
I have processes designed to capture end of period monthly data. Many times this data is not signed off on by the business until several days into the next month.
Given that I always use the last day of the month (20110831, etc) as my [Period Key], I find this very helpful.
I have to agree that it would have been better with all of the counterparts (EO & BO for week, month, and year). Not sure how they decided on just one.