Bad Habits to Kick: Date and time shorthand
September 20th, 201112
Bad Habits to Kick: Date and time shorthand
September 20th, 201112
 
 
This is part of more than a decade of posts involving Bad Habits and Best Practices.
See the full index.

I've come across quite a bit of code that uses date/time shorthand that can either be confusing or downright dangerous. There are three areas I want to focus on: shorthand for date arithmetic, shorthand for date parts, and explicitly using style numbers when you can't control input format. (Also see this short video on shorthand and this one on formats at MSSQLTips.com.)

Date Arithmetic

This one I mentioned quite briefly in a previous "Bad Habits to Kick" post, but I see it in use enough that I thought it deserved a grander call-out. I see a lot of code (and I have a lot of legacy code myself) using things like:

DECLARE @datetime_variable datetime = CURRENT_TIMESTAMP;
SET @datetime_variable = @datetime_variable - 1;

With DATETIME and SMALLDATETIME, this shorthand for DATEADD adds / subtracts the number specified in terms of days. This is because internally the date and time components of these types are stored as integers. Little-known fact: it also works with decimals, e.g.:

DECLARE @datetime_variable datetime = CURRENT_TIMESTAMP;
SET @datetime_variable = @datetime_variable - 0.5;

This subtracts 12 hours from the existing date/time value.

But my point is not to show you how cool this shorthand is, it is to show you how evil it can be. Let's say that you have upgraded to SQL Server 2008 or greater (finally), and now you're going back and re-visiting your old columns, indexes, variables, and parameters to see if you can squeeze any performance, precision or utility out of them. You might see the datetime_column related to the above variable and think, hey, what a great candidate to convert that column to a DATE or DATETIME2 data type. Well, as soon as you update the variable declaration above to say:

DECLARE @datetime_variable datetime2 = CURRENT_TIMESTAMP;

All of your shorthand date arithmetic code will stop working, with the following errors:

Msg 206, Level 16, State 2
Operand type clash: datetime2 is incompatible with int

…and…

Msg 206, Level 16, State 2
Operand type clash: datetime2 is incompatible with numeric

As far as I know, this is not something that tools like the Best Practices Analyzer and Upgrade Advisor are going to catch; further to that, keep in mind that this might not just be in your stored procedures and functions. I see formulas like this just as often in default constraints and computed column definitions.< A related issue is when you use this shorthand to strip time from a date:

DECLARE @d datetime = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);

This works great for the old types, but the following fails with the new types for the same reason as the shorthand arithmetic above:

DECLARE @d date = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);

Result:

Msg 206, Level 16, State 2
Operand type clash: int is incompatible with date

And no, that's not because I did not use the full DATEADD(DATEDIFF()) expression combination (which isn't necessary with the old types due to implicit conversion, but you should always use as a matter of best practice anyway).

It is actually much simpler to strip the time from today:

DECLARE @d date = CURRENT_TIMESTAMP;

For yesterday:

DECLARE @d date = DATEADD(DAY, -1, CURRENT_TIMESTAMP);

So the moral of this segment is simply to always explicitly state what you are doing, and stop using fancy shorthand that will not work as fluently as you might expect across all the systems where your code might end up. You'll type a little more, but your code will also be better insulated from date type changes.

Date Parts

Another form of shorthand that I see a lot is the variety of parameters for date parts. Do you think these are all the same?

SELECT DATEPART(y, GETDATE()), DATEPART(yy, GETDATE()), DATEPART(yyyy, GETDATE());
 
-- the first one is an alias for DAYOFYEAR (also known as dy)
-- the other two are aliases for YEAR

How about these?

SELECT DATEPART(w, GETDATE()), DATEPART(wk, GETDATE()), DATEPART(ww, GETDATE());
 
-- the first one is an alias for WEEKDAY (also known as dw)
-- the other two are aliases for WEEKOFYEAR

Pop Quiz: Match the short form on the left with the longer but proper name on the right. Don't actually draw lines on your screen, but keep track at home and see if you know them all by heart.

SELECT DATEADD(n,    1, @d);
SELECT DATEADD(mi,   1, @d);
SELECT DATEADD(mcs,  1, @d);
SELECT DATEADD(mm,   1, @d);
SELECT DATEADD(ns,   1, @d);
SELECT DATEADD(ms,   1, @d);
SELECT DATEADD(m,    1, @d);
SELECT DATEADD(MILLISECOND,  1, @d);
SELECT DATEADD(MONTH,        1, @d);
SELECT DATEADD(MICROSECOND,  1, @d);
SELECT DATEADD(MINUTE,       1, @d);
SELECT DATEADD(NANOSECOND,   1, @d);

I feel like I'm taking this quiz every time I see code like the values on the left. Don't make me think; spell out what you mean. Please. There is no gain to being cryptic here; type out the actual date part component you want to use so that all future readers will understand your intention on first sight. This can be crucial when troubleshooting issues with code and the last thing you want to struggle with is whether you've successfully committed that shorthand to memory.

Using style numbers when you can't control input format

Finally, the datepart functions like YEAR(), MONTH() etc. are quite commonly used against string data that is supposed to represent a date. There is a problem, though, with code like this:

SELECT YEAR('03/13/2011');

This may work fine on your development box where you live quite happily in your default language, dateformat, and regional settings. However, try it with one of the following language or dateformat settings (which you won't always be able to control when your code is deployed):

SET DATEFORMAT dmy;
SELECT YEAR('03/13/2011');

Results:

Msg 241, Level 16, State 1
Conversion failed when converting date and/or time from character string.

…or…

SET LANGUAGE FRENCH;
SELECT YEAR('03/13/2011');

Results:

Le paramètre de langue est passé à Français.
Msg 241, Level 16, State 1
Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères.

Ideally, you would change it so that a format like mm/dd/yyyy is never used (see this post for more details). If you can't control the format, but you know it's consistent (which isn't always possible), you should use code like this. Using a style number (like 101 for m/d/y, or 103 for d/m/y) will survive language and regional settings and force SQL Server to interpret the string in that style:

SET DATEFORMAT mdy;
SELECT YEAR(CONVERT(SMALLDATETIME, '03/13/2011', 101));
 
SET LANGUAGE FRENCH;
SELECT YEAR(CONVERT(SMALLDATETIME, '03/13/2011', 101));

Though to be a bit more explicit (and maybe a little too pedantic on my part), I'd actually prefer the following:

SELECT DATEPART(YEAR, CONVERT(DATE, '03/13/2011', 101));

And yes, magically, the 101 and 103 styles work with DATE as well.

Summary

I hope I've convinced you to be more explicit when dealing with date/time values. I know all too well that old habits are hard to kick, but I promise you that if you make an effort to be explicit, you'll have far less chance of being surprised (or worse) when the rules change from version to version.

This is part of more than a decade of posts involving Bad Habits and Best Practices.
See the full index.
By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

12 Responses

  1. Marc Brooks says:

    I've got a lot of DateTime stuff on a very old blog entry
    http://j.mp/SQLDateMagic

  2. AaronBertrand says:

    Thanks Paul, I use CURRENT_TIMESTAMP for two simple reasons: it's one of the few date/time related areas where Microsoft actually followed the standard (GETDATE() is proprietary), and because I don't have to type the parentheses everywhere (when building complex expressions it can really reduce eyestrain having to match up those parentheses).

  3. Paul says:

    Thanks for the excellent advice.  On a somewhat related note, I noticed you use CURRENT_TIMESTAMP in your examples.  Is there any advantage to using this instead of GETDATE(), which is what I've always used?
    Thanks

  4. Alejandro Mesa says:

    Hi Aaron,
    Another way to add date and time together, and don't have to worry about time precision, is adding the difference in days between an anchor date and the date variable to the time variable, after casting it to datetime2 or datetime depending on the case.
    Something to keep in mind, is that the lower value for date / datetime2 is '00010101', but when you cast time to datetime2, SQL Server will use '19000101', so be sure you are not working with dates lower than '19000101' or you will have to do some acrobatics.
    DECLARE @d DATE = '2011-01-01', @t TIME(4) = '23:45:45.3456';
    SELECT
    CONVERT(varchar(35), DATEADD([day], DATEDIFF([day], '19000101', @d), CONVERT(datetime2, @t)), 126) AS dt2
    GO

    AMB

  5. gbn says:

    Thought it'd be like this: just wondered if there was a better way
    Cheers

  6. AaronBertrand says:

    gbn, unfortunately I don't know of an easy or efficient way to add these values together. Here are two ideas, given these variables:
    DECLARE @d DATE = '2011-01-01', @t TIME(0) = '23:45';
    (1) cast them as datetime/smalldatetime/datetime2 after concatenating:
    SELECT CONVERT(DATETIME, CONVERT(CHAR(10), @d, 120) + 'T' + CONVERT(CHAR(5), @t, 108));
    (2) convert the date to datetime, then add the difference between midnight and the time value.
    SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '00:00', @t), CONVERT(DATETIME, @d));
    (If you wanted an explicit date type other than DATETIME, you could wrap that with a convert.)
    In each case you may want to cater to the precision you're after, e.g. in (1) you can move to CHAR(8) if you want seconds, if you want more than that (1) isn't your option. For (2) you could use MILLISECOND, MICROSECOND, even NANOSECOND depending on the precision of your time value.

  7. gbn says:

    What would be the easiest to "add" a time value and a date value using the new types?
    http://stackoverflow.com/q/7475901/27535

  8. AaronBertrand says:

    Thanks SQLChap, I talked about that in my previous post as well. In fact it was the very first thing I mentioned:
    /blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

  9. SQLChap says:

    For unambiguous dates we prefer to use reversed notation e.g.
    20110313
    Then it's clear both to the person reading it and the SQL Server which value is which.

  10. AaronBertrand says:

    Paul, yes, I know SQL Server 2008 is a lot better at dealing with convert to DATE. I shouldn't have used DATE as the type in that example, since it could have just as easily been:
    DECLARE @d DATE = CURRENT_TIMESTAMP;
    It makes more sense in the context of DATETIME/SMALLDATETIME and on versions prior to SQL Server 2008. Which, of course, are still quite healthy out there.

  11. Paul White says:

    Hi Aaron,
    For stripping times from dates, we can CONVERT(DATE, value) directly.  Prior to 2008 we're stuck with the more verbose methods, of course.
    The 'bad habit to kick' this post reminded me of is being sloppy with types – in general.  T-SQL doesn't (yet) help us by providing a concise way to specify the type of a constant (at least not for all types) but your last 'pedantic' example is required in an indexed view for example (since even implicit conversions from string to a date format are considered non-deterministic).
    It's possible to go too nuts with types, especially where numeric/decimal arithmetic operations are performed, but in general I find it useful to be as precise (pedantic) as reasonably practical.
    Paul

  12. Tracy McKibben says:

    Thank you for this!  I hate reading code that is full of shorthand abbreviations, especially the date/time stuff.  Don't be lazy, spell EVERYTHING out in full!