SQL Server DATE

Summary: in this tutorial, you will learn how to use the SQL Server DATE to store date data in a table.

Introduction to SQL Server DATE

To store the date data in the database, you use the SQL Server DATE data type. The syntax of DATE is as follows:

DATE
Code language: SQL (Structured Query Language) (sql)

Unlike the DATETIME2 data type, the DATE data type has only the date component. The range of a DATE value is from January 1, 1 CE (0001-01-01) through December 31, 9999 CE (9999-12-31).

It takes 3 bytes to store a DATE value. The default literal string format of a DATE value is as follows:

YYYY-MM-DDCode language: SQL (Structured Query Language) (sql)

In this format:

• YYYY is four digits that represent a year, which ranges from 0001 to 9999.
• MM is two digits that represent a month of a year, which ranges from 01 to 12.
• DD is two digits that represent a day of the specified month, which ranges from 01 to 31, depending on the month.

SQL Server DATE examples

A) Query data from a table based on DATE values

Let’s see the sales.orders table from the sample database:

The following example returns all orders whose ordered date is earlier than January 05 2016:

SELECT    
	order_id, 
	customer_id, 
	order_status, 
	order_date
FROM    
	sales.orders
WHERE order_date < '2016-01-05'
ORDER BY 
	order_date DESC;Code language: SQL (Structured Query Language) (sql)

Here is the output:

B) Using DATE to define the table columns example

The following statement creates a table named sales.list_prices that has two DATE columns:

CREATE TABLE sales.list_prices (
    product_id INT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    amount DEC (10, 2) NOT NULL,
    PRIMARY KEY (
        product_id,
        valid_from,
        valid_to
    ),
    FOREIGN KEY (product_id) 
      REFERENCES production.products (product_id)
);
Code language: SQL (Structured Query Language) (sql)

The following INSERT statement illustrates how to insert a row with literal date values into the table:

INSERT INTO sales.list_prices (
    product_id,
    valid_from,
    valid_to,
    amount
)
VALUES
    (
        1,
        '2019-01-01',
        '2019-12-31',
        400
    );
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server DATE data type to store date data in a table.