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 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.
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
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
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
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:
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
If we’re still using
DATETIME data types in our database, we should not run queries where the
DATETIME parameter ends in
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.
DATETIME horror stories with me on Twitter at @bornsql.