Last year I ran a series of posts about Database Fundamentals.
Over the next few weeks, I will cover the basics of various date and time data types in SQL Server, when to use them, and which functions to use for date and time calculations.
This week will start with probably the best-known data type, DATETIME.
DATETIME is an eight-byte data type which stores both a date and time in one column, with an accuracy of three milliseconds. As we’ll see though, the distribution of this granularity may not be exactly what we’d expect.
Valid DATETIME values are January 1, 1753 00:00:00.000, through December 31, 9999 23:59:59.997. On older databases designed prior to SQL Server 2008, because there was no explicit support for date values, it was sometimes customary to leave off the time portion of a DATETIME value, and have it default to midnight on that morning. So for example today would be stored as February 21, 2018 00:00:00.000.
Let’s say we want to find and store today’s date, at our current local time. Running SELECT GETDATE(); will return the local date and time on our SQL Server instance as a DATETIME value. Other functions exist that return values in different ways, but we will cover those in future posts.
For example, running the above query, we can see that it’s currently February 21, 2018 09:03:12.343 in my local time zone. If I was able (and I’m not) to run the same SELECT statement exactly one millisecond later, I would not get .344 as the last three digits, because DATETIME only allows for a three-millisecond granularity. Depending on a number of rounding factors, I might get back February 21, 2018 09:03:12.343 again, or possibly February 21, 2018 09:03:12.347.
In other words, the millisecond value for any DATETIME is one of only 0, 3 or 7.
One major side effect of this is that we could accidentally use the wrong value in a range query, if we don’t know the exact data type. Let’s say we’re trying to find all valid values between the start and end of a certain day.
We might try to use the following query (which is incorrect):
-- This won't work for the entire day range
SELECT *
FROM table
WHERE column1 BETWEEN 'February 21, 2018 00:00:00.000'
AND 'February 21, 2018 23:59:59.999';
Since the second parameter of the BETWEEN keyword is not a valid DATETIME value, the query may return more rows than expected, because the ‘.999’ will round up to the nearest DATETIME, which will be midnight on the morning of the following day.
(You can prove this by running SELECT CAST('February 21, 2018 23:59:59.999' AS DATETIME); and reviewing the output.)
Our query can be rewritten as follows:
-- This will return rows for the entire day range
SELECT *
FROM table
WHERE column1 >= 'February 21, 2018 00:00:00.000'
AND column1 <= 'February 21, 2018 23:59:59.997';
There are two interesting things to point out in the second query:
- The
BETWEENkeyword is gone, and replaced with a range based on the column. It is easier to understand the parameters, and clear what we’re looking for. (Greater than or equal to midnight, and less than or equal to 3 milliseconds before midnight). - The second parameter ends with
.997.
If we’re still using DATETIME data types in our database, we should not run queries where the DATETIME parameter ends in .999.
Thankfully, Microsoft introduced new date and time data types with SQL Server 2008 to resolve this silliness. We will get into those over the next few weeks.
Remember, if we want to do range queries with DATETIME values, we must take the 3 millisecond granularity into account, and avoid using the BETWEEN keyword. It’s quite easy to accidentally leave off the time portion of a DATETIME, which would default to midnight on the morning of that date.
Share your DATETIME horror stories with me on Twitter at @bornsql.