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:

…and…

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:

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:

…or…

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

Results:

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.