DateTime2 vs DateTime in SQL Server ?

In SQL Server, DATETIME and DATETIME2 are both data types used to store date and time values, but they differ in precision, storage size, date range, and functionality. Below is a detailed comparison with examples:

1. Precision

DATETIME
Accuracy: 3.33 milliseconds (rounded to increments of .000, .003, or .007 seconds).
Example: 2023-10-05 12:34:56.123 is stored as 2023-10-05 12:34:56.123.
However, 2023-10-05 12:34:56.125 is rounded to 2023-10-05 12:34:56.127.
DATETIME2
Accuracy: 100 nanoseconds (configurable precision up to 7 decimal places for fractional seconds).
Example: 2023-10-05 12:34:56.1234567 is stored as-is if precision is set to 7.

2. Storage Size

DATETIME
Fixed 8 bytes (4 bytes for date, 4 bytes for time).
DATETIME2
Variable storage based on precision:
- 6–8 bytes (6 bytes for precision ≤ 2, 7 bytes for precision 3–4, 8 bytes for precision 5–7).
Example:
- DATETIME2(3) uses 7 bytes.
- DATETIME2(7) (default) uses 8 bytes.

3. Date Range

DATETIME
January 1, 1753 to December 31, 9999.
DATETIME2
January 1, 0001 to December 31, 9999.
Supports older historical dates (e.g., 0001-01-01).

4. Syntax and Declaration

DATETIME

  CREATE TABLE Example (
      EventTime DATETIME
  );

DATETIME2
Allows specifying fractional seconds precision (0–7):

  CREATE TABLE Example (
      EventTime DATETIME2(3) -- 3 decimal places for fractional seconds
  );

5. Examples

Example 1: Precision Comparison

DECLARE 
    @dt DATETIME = '2023-10-05 12:34:56.125',
    @dt2 DATETIME2(7) = '2023-10-05 12:34:56.125';

SELECT 
    @dt AS [DateTime],          -- Output: 2023-10-05 12:34:56.127
    @dt2 AS [DateTime2];        -- Output: 2023-10-05 12:34:56.1250000

Example 2: Date Range

-- Works for DATETIME2 but fails for DATETIME
DECLARE @dt2_historic DATETIME2 = '0001-01-01';
DECLARE @dt_historic DATETIME = '0001-01-01'; -- Error: Out of range

Example 3: Storage Size

-- Check storage size using system views
SELECT 
    name, 
    system_type_name,
    max_length AS storage_bytes
FROM sys.dm_exec_describe_first_result_set(N'
    SELECT 
        CAST(''2023-10-05'' AS DATETIME) AS dt,
        CAST(''2023-10-05'' AS DATETIME2(7)) AS dt2
', NULL, 0);

Result:

name	system_type_name	storage_bytes
dt	datetime	8
dt2	datetime2(7)	8

6. When to Use Which?

Use DATETIME2 if:
You need fractional seconds precision beyond 3 milliseconds.
You require dates earlier than 1753 (e.g., historical data).
You want to optimize storage (e.g., DATETIME2(3) uses 7 bytes vs. DATETIME’s 8 bytes).
Use DATETIME if:
You work with legacy systems that require compatibility.

7. Key Takeaways

Feature	`DATETIME`	`DATETIME2`
Precision	3.33 milliseconds	100 nanoseconds (up to 7 decimals)
Storage	8 bytes	6–8 bytes (depends on precision)
Date Range	1753–9999	0001–9999
Flexibility	Fixed precision	Configurable precision

8. Best Practice

Microsoft recommends DATETIME2 for new development due to its flexibility, precision, and extended date range.
Use DATETIME only for backward compatibility with older systems.

-- Always prefer DATETIME2
CREATE TABLE ModernTable (
    LogTime DATETIME2(7) DEFAULT SYSDATETIME()
);