SQL Server DATE Data Type

SQL Server DATE stores a calendar date without a time of day or time-zone offset. Its range is 0001-01-01 through 9999-12-31, and its accuracy is one day. See Microsoft’s DATE documentation.

Syntax

The syntax for the DATE data type is as follows:

DATE

Usage

Use DATE when a calendar day is sufficient, such as an order date or birthday. To add intervals or extract date parts, see the SQL Server date and time function reference. If a value also needs a time of day, use TIME, DATETIME2, or DATETIMEOFFSET, depending on whether you need a time only, a combined date/time, or a preserved UTC offset.

Examples

Here are two examples of using the DATE data type:

Example 1: Inserting values with the DATE data type

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE
);

INSERT INTO orders (order_id, order_date) VALUES
    (1, '2022-01-01'),
    (2, '2022-02-14'),
    (3, '2022-03-23');

In the example above, we created a table named orders with two columns, order_id and order_date, where the data type of order_date is DATE. We inserted three order data and specified the date values using the ISO 8601 format.

Example 2: Performing date calculations using the DATE data type

SELECT order_id, order_date, DATEADD(day, 7, order_date) AS new_order_date
FROM orders;

In the example above, we used the DATEADD function to add 7 days to the date value in the order_date column, and returned the result as a new column named new_order_date.

Conclusion

Use DATE for calendar dates when time-of-day and offset information are not needed. For date arithmetic and extraction, see the SQL Server date and time function reference; for a date combined with a time, compare DATETIME2 and DATETIMEOFFSET.