Now, what sort of productivity or performance gains do you think you might have gained by typing
D instead of spelling out
DAY? Here's why you're going to care: some shorthand is not what you would expect, and could be very problematic if you're troubleshooting at 3 AM. This is based on a quiz I've given to hundreds of attendees of my Bad Habits sessions, and maybe three people have passed in all that time:
SELECT w = DATEPART(W, '20151223'), y = DATEPART(Y, '20151223'); /* results: w y ------ ------ 4 357 */
Admit it, it's not what you expected either, is it?
W stands for
Y stands for
YEAR. Please, just spell it out. You avoid these problems and make your code self-documenting, at the mere cost of an additional 32 milliseconds or so of typing. And I promise the extra characters won't slow down your queries.
Another quick one, this:
DECLARE @d datetime = GETDATE(); SELECT @d - 1;
Fails when you use the new types:
DECLARE @d date = GETDATE() - 1;
Msg 206, Level 16, State 2, Line 1 Operand type clash: date is incompatible with int
When adding or subtracting days from a date or datetime, just always use
DATEADD(), even though it's a bit more to type.
Both of these are explained in more detail in "Bad Habits to Kick : Using shorthand with date/time operations."
When I see date range queries that use
BETWEEN, I shudder. While it's very easy to find the beginning of a range, how exactly do you find the end? Unless you're dealing exclusively with the
date data type (or
datetime with reliable constraints preventing time), there isn't a trustworthy way to find the end, due to the varying precision and rounding behaviors of the different date/time types, as well as the problem known as February. And no,
EOMONTH() is not the solution, as it just gets the last day of the given month, at midnight, which is only useful if you don't store time.
It is always possible to use an open-ended range, even when using the
date data type (again, don't use
date, as it just makes your technique inconsistent). This is because it's always easier to find the beginning of the next range than the end of the current one. Take a month, for example. I can always get the beginning of the current month like this:
DECLARE @start datetime, @end datetime; SET @start = CONVERT(date, DATEADD(DAY, -DATEPART(DAY, GETDATE()), GETDATE())); /* result: 2016-04-01 */
Finding the "end" of the month is harder, because I have to know what data type I'm dealing with. I could use this common technique:
SET @end = DATEADD(MILLISECOND, -3, DATEADD(MONTH, 1, @start)); /* result: 2016-04-30 23:59:59.997 */
That's great, as long as the data type of all of the variables, parameters, columns, and both implicit and explicit conversions along the way stay as
datetime. If the parameter changes to
smalldatetime, your "end" now rounds up to May 1st at midnight. And if the column changes to
datetime2, there's a lottery-style chance you could lose data (since a row with
2016-04-30 23:59:59.9984132 could theoretically be stored).
This is much more reliable:
SET @end = DATEADD(MONTH, 1, @start); -- now the query changes from: ... WHERE [column] BETWEEN @start AND @end; -- to the slightly more verbose: ... WHERE [column] >= @start AND [column] < @end;
There is no chance you can include too much data or leave some out when you say "greater than or equal to the beginning of this range, and less than the beginning of the next." More information in "What do BETWEEN and the devil have in common?"
There you have it, four quick tips on dating responsibly, in as few words as I know how. Hope you've found it useful!