September 22, 2011 | SQL Server

SQL Server v.Next (Denali) : Will you use EOMONTH()?

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!
 

12 comments on this post

    • Kenneth Franklin - September 23, 2011, 12:18 AM

      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.

    • AaronBertrand - September 23, 2011, 12:23 AM

      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?

    • Ben Thul - September 23, 2011, 12:37 AM

      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.

    • AaronBertrand - September 23, 2011, 12:50 AM

      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')

    • cinahcaM madA - September 23, 2011, 5:46 AM

      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.

    • Paul White - September 23, 2011, 6:50 AM

      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 πŸ™‚

    • AaronBertrand - September 23, 2011, 4:39 PM

      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.

    • Henry Treftz - September 23, 2011, 4:43 PM

      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.

    • AaronBertrand - September 23, 2011, 4:51 PM

      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…

    • Kenneth Franklin - September 23, 2011, 7:59 PM

      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?

    • AaronBertrand - September 23, 2011, 8:09 PM

      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?

    • Kenneth Franklin - September 23, 2011, 10:43 PM

      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.

Comments are closed.