SQL Server DATETIME Data Type

The DATETIME data type in SQL Server represents a combination of date and time values, used to store dates and times between January 1, 1753 and December 31, 9999.


The syntax for the DATETIME data type is as follows:


Use Cases

The DATETIME data type is commonly used to store date and time information that needs to be accurate to the minute. It can be used to record a timestamp of when an event occurred, or to record the time that a record was created or modified.


Here are three examples of using the DATETIME data type:

Example 1: Inserting Data with DATETIME

Consider an order management system where we can use the DATETIME data type to store the order creation time and the last update time.

First, create a table named orders to store order information:

   order_id INT PRIMARY KEY,
   customer_name VARCHAR(50) NOT NULL,
   order_date DATETIME NOT NULL,
   last_update DATETIME

Now we can insert some data into the orders table:

INSERT INTO orders (order_id, customer_name, order_date, last_update)
  (1, 'John Smith', '2022-03-10 10:23:35', '2022-03-11 13:25:20'),
  (2, 'Mary Johnson', '2022-03-11 14:12:07', NULL),
  (3, 'David Lee', '2022-03-11 16:18:50', '2022-03-11 18:30:12');

Here, we inserted three orders. The first order was created by John Smith on March 10, 2022, at 10:23:35 AM, and last updated on March 11, 2022, at 1:25:20 PM. The second order was created by Mary Johnson on March 11, 2022, at 2:12:07 PM, with no last update time. The third order was created by David Lee on March 11, 2022, at 4:18:50 PM, and last updated on March 11, 2022, at 6:30:12 PM.

Now we can query the orders table to see the creation and last update times for all orders:

SELECT order_id, customer_name, order_date, last_update
FROM orders;

Running the above query will give us the following results:

order_id customer_name order_date last_update
1 John Smith 2022-03-10 10:23:35.000 2022-03-11 13:25:20.000
2 Mary Johnson 2022-03-11 14:12:07.000 NULL
3 David Lee 2022-03-11 16:18:50.000 2022-03-11 18:30:12.000

Example 2

Suppose we have an orders table used to store order information, and we want to add a column to record the order creation time.


This will add a column named created_at with a default value of the current date and time.

Example 3

Suppose we want to query all orders created after January 1st, 2022.

SELECT * FROM orders WHERE created_at >= '2022-01-01 00:00:00';

This will return all order records created after January 1st, 2022.


In SQL Server, the DATETIME data type is a commonly used data type for dates and times, used to store date and time information that needs to be accurate to the minute. It is suitable for recording timestamps of events, or recording the creation and modification times of records.