Datetime2 vs DateTime in SQL Server

Efficient storage & precision management

Every byte counts! Choose DateTime2 for potential storage savings. By controlling the precision level, from 0 to 7, you can vary the storage space between 6 to 8 bytes. The fixed 8 bytes for DateTime seems comparably inflexible:

DECLARE @TimeWithCustomPrecision DateTime2(3) = '2023-04-01 12:34:56.789'; -- Cuttin' it down to size!
SELECT @TimeWithCustomPrecision;


Compliance with SQL standards

Respect the standards! DateTime2 aligns with the SQL standard, ensuring compatibility and maintaining standards hygiene. When collaborating with .NET applications, it's crucial to harmonize System.DateTime with DateTime2 to avoid any rounding-off nightmares:

Catering to unique data scenarios

Go global with datetimeoffset, the ideal choice for time zone awareness. When you're dealing with only dates or times, consider using DATE and TIME data types respectively, optimizing space consumption further.

Safe calculations & Legacy compatibility

Particular operations like calculating age or scheduling tasks require precise date manipulations. The precision of DateTime2 proves beneficial here. Just remember to use the correct functions, like DateAdd:

DECLARE @BirthDate DateTime2 = '2000-05-12 08:23:22.120'; -- Happy birthday!
DECLARE @CurrentDate DateTime2 = SYSDATETIME();
SELECT DATEDIFF(YEAR, @BirthDate, @CurrentDate) AS Age; -- How old am I?


However, if you're still living with dinosaurs 🦕 in legacy systems, you would need to stick with DateTime to avoid disruptive, haunting compatibility issues!

Threading on data type conversion paths

Be wary when casting between datetime and datetime2 as you may lose precision. In case you try to downcast DateTime2 to DateTime, be ready for fractions of seconds getting chopped off:

DECLARE @HighPrecisionTime DateTime2 = '2023-04-01 12:34:56.7891011'; -- Too precise for DateTime!
DECLARE @LowerPrecisionTime DateTime = @HighPrecisionTime;
SELECT @LowerPrecisionTime; -- Who kidnapped my precision?!


Such precision loss could be devastating, especially for systems that rely on precise timestamps, such as logging or synchronization services.