SQL Server DATETIME Data Type

The SQL Server DATETIME data type combines a date and time, with a range from January 1, 1753, through December 31, 9999. It has no time-zone offset and rounds fractional seconds to .000, .003, or .007 seconds.

Syntax

The syntax for the DATETIME data type is as follows:

DATETIME

Use Cases

DATETIME is often encountered in existing SQL Server schemas. Its fractional-second precision is finer than one minute, but values are rounded to .000, .003, or .007 seconds rather than stored with user-defined precision. Microsoft advises using the DATE, TIME, DATETIME2, or DATETIMEOFFSET types for new work. See Microsoft’s date and time type guidance, or compare the SQLiz DATE, TIME, and DATETIME2 references.

Examples

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:

CREATE TABLE orders (
   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)
VALUES
  (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:

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.

ALTER TABLE orders ADD created_at DATETIME NOT NULL DEFAULT GETDATE();

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.

Conclusion

DATETIME stores a date and time without a time-zone offset, and rounds fractional seconds to .000, .003, or .007. For new schemas, choose a type that matches the required range, precision, and offset behavior; Microsoft recommends the newer DATE, TIME, DATETIME2, or DATETIMEOFFSET types instead of DATETIME.