PrismitaLEARN · PRACTICE · GROW

What is the difference between DATETIME and DATETIME2?

SQL Server Design Basic Fresher1–2 Yrs Conceptual 1 min read

Quick Answer

DATETIME is the legacy type: range from 1753, precision rounded to ~3.33 ms, 8 bytes. DATETIME2 is the modern replacement: range from year 1, precision to 100 ns (configurable, DATETIME2(3) is 7 bytes), ANSI-compliant. Use DATETIME2 for new columns; use DATETIMEOFFSET when you need the timezone offset.

DATETIME is the old type — it only goes back to 1753, its precision is rounded to about three milliseconds, and it's eight bytes. DATETIME2 is the one to use now: wider range, precision you can configure down to 100 nanoseconds, and it can actually be smaller on disk if you pick a lower precision. For new columns I use DATETIME2, store times in UTC, and reach for DATETIMEOFFSET only when I genuinely need the timezone offset preserved. And I use SYSUTCDATETIME() rather than GETUTCDATE() so I'm not mixing types.

  • DATE and TIME exist separately since 2008.
  • DATETIMEOFFSET stores the offset from UTC — best for globally distributed apps; otherwise store UTC in a DATETIME2 and convert in the app.
  • Functions: SYSDATETIME() / SYSUTCDATETIME() return DATETIME2; GETDATE() / GETUTCDATE() return the old DATETIME.
  • Mixing DATETIME and DATETIME2 in comparisons causes implicit conversion — pick one type per column and stick to it.
All SQL Server questions More on Design