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.

Syntax

The syntax for the DATETIME data type is as follows:

DATETIME

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.

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

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.