DB-유

SQL Server Date and Time Types: datetime vs datetime2

· mssql, tsql, data-types

datetime has been in SQL Server since the beginning, and most schemas I inherit use it everywhere. datetime2 arrived in SQL Server 2008 and is better in almost every respect: wider range, configurable precision, smaller storage at equal or better accuracy, and ANSI-compliant. Yet the two behave differently in ways that break code when you switch, especially around rounding and implicit conversion. I learned that by "fixing" a column type and watching a month-end report change its totals. Below are the differences with runnable examples, and a migration approach that doesn't silently change query results. It all applies to SQL Server 2012 through 2019.

The types side by side

  • datetime: 8 bytes, range 1753-01-01 to 9999-12-31, accuracy to 1/300 of a second (values end in .000, .003 or .007).
  • datetime2(n): 6 to 8 bytes, range 0001-01-01 to 9999-12-31, precision 0 to 7 fractional digits (100 ns at 7). datetime2(3) is 7 bytes and is the direct millisecond equivalent; datetime2(7) is the default when no precision is given.
  • smalldatetime: 4 bytes, to the minute, range up to 2079-06-06. Avoid for new work.
  • date: 3 bytes, date only. time(n): 3 to 5 bytes, time only.
  • datetimeoffset(n): datetime2 plus a UTC offset, 8 to 10 bytes. Use it when you actually store the zone; storing UTC in datetime2 and the zone elsewhere is usually simpler.

Rounding

DECLARE @dt datetime = '2020-05-04 10:15:30.999';
DECLARE @dt2 datetime2(3) = '2020-05-04 10:15:30.999';
SELECT @dt AS dt, @dt2 AS dt2;

@dt shows 10:15:31.000. datetime rounds to the nearest 1/300 s, so .999 becomes the next second. This is why WHERE OrderDate < '2020-05-05 00:00:00.000' and WHERE OrderDate <= '2020-05-04 23:59:59.999' are equivalent with datetime but the second one is wrong with datetime2, where 23:59:59.999 is a distinct value from midnight. The safe pattern is always >= start AND < next_day. I write every date range that way now, whatever the type.

Literal parsing

datetime interprets '2020-05-04' correctly but '04.05.2020' or '2020-05-04 10:15' with SET DATEFORMAT and language settings in the loop, and the unseparated '20200504' is the only fully safe string. datetime2 and date treat the ISO 8601 form '2020-05-04' and '2020-05-04T10:15:30' as unambiguous regardless of DATEFORMAT:

SET DATEFORMAT dmy;
SELECT CAST('2020-05-04' AS datetime) AS as_datetime, CAST('2020-05-04' AS datetime2) AS as_datetime2;

With dmy, the datetime cast fails or flips the month depending on the value; the datetime2 cast is stable. Another reason to move.

Implicit conversions and precedence

datetime2 has higher precedence than datetime. When you compare the two, the datetime side is converted up. That is harmless for indexed columns on the datetime2 side, but the reverse pattern hurts: a datetime2 parameter compared against a datetime column forces a conversion on every row and defeats the index seek.

DECLARE @p datetime2(7) = SYSDATETIME();
SELECT COUNT(*) FROM dbo.Orders WHERE OrderDate = @p;   -- OrderDate is datetime: implicit CONVERT on the column

Look for CONVERT_IMPLICIT in the execution plan's seek or scan predicate. ORMs that map .NET DateTime to datetime2 by default (Entity Framework does) cause this constantly against old datetime schemas. Fix by matching the column type or by declaring parameters as datetime.

The functions

GETDATE() returns datetime; SYSDATETIME() returns datetime2(7); SYSUTCDATETIME() is the UTC version and SYSDATETIMEOFFSET() includes the server offset. DATEADD, DATEDIFF and DATEPART all accept datetime2. Note DATEDIFF returns int and overflows on millisecond differences over about 24 days; DATEDIFF_BIG (SQL Server 2016) fixes that.

SELECT SYSDATETIME(), SYSUTCDATETIME(), DATEDIFF_BIG(MILLISECOND, '2000-01-01', SYSDATETIME());

Migrating a column

Changing the type in place is a size-of-data operation on datetime to datetime2(3) because the storage differs. Do it in a maintenance window, or add a new column and backfill in batches:

ALTER TABLE dbo.Orders ALTER COLUMN OrderDate datetime2(3) NOT NULL;

Before this, drop indexes and constraints that reference the column and recreate them after. Grep the code base for 23:59:59.997 and .999 boundary tricks and for string formats with CONVERT(varchar, col, 121) that assume exactly three fractional digits; datetime2(7) output has seven, which breaks fixed-width parsers downstream.

Pick a precision deliberately. I default to datetime2(3) because it matches what the application had; datetime2(0) saves a byte if you never need sub-second; datetime2(7) is rarely useful and makes every value one byte larger than datetime.

Checking your work

SELECT c.name, t.name AS type_name, c.scale, c.max_length
FROM sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Orders') AND t.name LIKE '%date%';

Then run the application's date-range reports for a known period against a pre-migration copy and compare row counts; any difference is a boundary predicate that relied on datetime rounding.

Notes

"Msg 242: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." A string before 1753 or an ambiguous day/month order. datetime2 accepts years from 0001; the message for datetime2 is 241, "Conversion failed when converting date and/or time from character string".

"Msg 8115: Arithmetic overflow error converting expression to data type datetime." You are adding or subtracting numbers directly (GETDATE() + 1). That works on datetime because it is internally a float-like pair, but datetime2 does not support arithmetic operators. Use DATEADD.

Sorting or grouping by date suddenly slow. Check for CONVERT_IMPLICIT from the parameter type mismatch (see the implicit conversions section) before touching indexes.

datetime2 and linked servers or older drivers. Very old clients (SQL Server Native Client 9, ODBC drivers from the 2005 era) return datetime2 as a string. Anything from SQL Server 2008 onwards handles it natively.

For new columns: date when you only need the day, datetime2(3) otherwise, and store UTC. Old datetime columns I only migrate when I'm in the table anyway.