Bad Habits to Kick : Using shorthand with date/time operations
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.)
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:
Operand type clash: datetime2 is incompatible with int
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);
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;
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.
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:
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');
Conversion failed when converting date and/or time from character string.
SET LANGUAGE FRENCH; SELECT YEAR('03/13/2011');
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.
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.
See the full index.
I've got a lot of DateTime stuff on a very old blog entry
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).
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?
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';
CONVERT(varchar(35), DATEADD([day], DATEDIFF([day], '19000101', @d), CONVERT(datetime2, @t)), 126) AS dt2
Thought it'd be like this: just wondered if there was a better way
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.
What would be the easiest to "add" a time value and a date value using the new types?
Thanks SQLChap, I talked about that in my previous post as well. In fact it was the very first thing I mentioned:
For unambiguous dates we prefer to use reversed notation e.g.
Then it's clear both to the person reading it and the SQL Server which value is which.
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.
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.
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!