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.