datetime (Transact‑SQL)

The datetime data type stores date and time data ranging from 1753‑01‑01 00:00:00.000 through 9999‑12‑31 23:59:59.997. It is stored as two 4‑byte integers: one for the date and one for the time.

Syntax

datetime [ (precision) ]

Precision (scale) is ignored; datetime always has a scale of 3 (millisecond accuracy).

Storage Size

Data Type	Storage Size
datetime	8 bytes

Range and Accuracy

Component	Supported Range	Accuracy
Date	January 1, 1753 – December 31, 9999	1 day
Time	00:00:00 – 23:59:59.997	3.33 ms (≈ .000, .003, .007)

Usage Examples

Creating a Table

CREATE TABLE dbo.OrderLog
(
    OrderID   INT PRIMARY KEY,
    OrderDate DATETIME NOT NULL,
    Amount    MONEY
);

Inserting Values

INSERT INTO dbo.OrderLog (OrderID, OrderDate, Amount)
VALUES
  (1, '2024-04-30 14:23:45.123', 1500.00),
  (2, GETDATE(), 250.75);

Retrieving Data

SELECT OrderID,
       CONVERT(varchar(20), OrderDate, 120) AS OrderDate,
       Amount
FROM dbo.OrderLog
WHERE OrderDate > '2024-01-01';

Conversion Functions

Common functions to work with datetime values:

GETDATE() – Returns the current system date and time.
CAST(expression AS datetime) – Explicitly converts an expression to datetime.
CONVERT(datetime, expression [, style]) – Converts with optional formatting style.
DATEADD(datepart, number, date) – Adds an interval to a date.
DATEDIFF(datepart, startdate, enddate) – Returns the count of datepart boundaries.

Best Practices

Prefer datetime2 for new development – it offers a larger range and higher precision.
Avoid mixing datetime with string literals without an explicit CONVERT or CAST to prevent locale‑dependent parsing.
When storing only dates, use date to save space and avoid time‑component confusion.