THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

Bad Habits to Kick : Using shorthand with date/time operations

I've come across quite a bit of code that uses date/time shorthand that can either be confusing or downright dangerous. There are two areas I want to focus on: shorthand for date arithmetic, and shorthand for date parts.

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, Line 3
Operand type clash: datetime2 is incompatible with int
...and...
Msg 206, Level 16, State 2, Line 3
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, Line 1
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).

Unfortunately you're going to need to do this the long way:

DECLARE @d DATE = DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101');

While the following works, I personally think it is less self-documenting and prone to questions and/or confusion about what '0' means:

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

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.

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 collation, mdy dateformat and US English setting. However, try it with one of the following language or dateformat settings (which you want always be able to control when your code is deployed):

SET DATEFORMAT mdy;
SELECT YEAR('03/13/2011');
Results:
Msg 241, Level 16, State 1, Line 2
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, Line 2
Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères.

To deal with this correctly, assuming you know the incoming format (this isn't always possible), you should use code like this, which will survive language and regional settings:

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.
 

Published Tuesday, September 20, 2011 9:31 AM by AaronBertrand

Comments

 

Tracy McKibben said:

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!

September 20, 2011 8:41 AM
 

Paul White said:

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

September 20, 2011 9:17 AM
 

AaronBertrand said:

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.

September 20, 2011 9:28 AM
 

SQLChap said:

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.

September 20, 2011 10:33 AM
 

AaronBertrand said:

Thanks SQLChap, I talked about that in my previous post as well. In fact it was the very first thing I mentioned:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

September 20, 2011 10:43 AM
 

gbn said:

What would be the easiest to "add" a time value and a date value using the new types?

http://stackoverflow.com/q/7475901/27535

September 20, 2011 12:32 PM
 

AaronBertrand said:

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.

September 20, 2011 12:41 PM
 

gbn said:

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

Cheers

September 20, 2011 12:48 PM
 

Alejandro Mesa said:

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

September 20, 2011 2:04 PM
 

Paul said:

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

September 22, 2011 4:34 PM
 

AaronBertrand said:

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

September 22, 2011 4:44 PM
 

Marc Brooks said:

I've got a lot of DateTime stuff on a very old blog entry

http://j.mp/SQLDateMagic

May 6, 2013 12:38 AM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement