SQL Server DATETIME2 Data Type

The DATETIME2 data type is a new date/time data type introduced in SQL Server 2008. It has a larger time range and higher precision than the DATETIME data type.

Syntax

The syntax of the DATETIME2 data type is as follows:

DATETIME2(p)

Where p is an optional parameter that represents the precision of the time (i.e., the number of decimal places) with a default value of 7. The valid precision range is from 0 to 7.

The time range that DATETIME2 data type can store is from “0001-01-01 00:00:00.0000000” to “9999-12-31 23:59:59.9999999”, with a precision of up to 100 nanoseconds.

Usage

The DATETIME2 data type is typically used in scenarios that require high-precision time, such as financial trading systems, scientific experiment records, etc. Additionally, it is also suitable for scenarios that require the storage of historical data because it supports a larger time range.

Examples

Example 1

The following example uses the DATETIME2 data type to store the start and end times of a scientific experiment:

CREATE TABLE Experiment (
    ID INT PRIMARY KEY,
    StartTime DATETIME2(3) NOT NULL,
    EndTime DATETIME2(3) NOT NULL
);

INSERT INTO Experiment (ID, StartTime, EndTime)
VALUES (1, '2022-03-10 09:00:00.000', '2022-03-10 12:30:15.789');

SELECT * FROM Experiment;

In the above example, we create a table called Experiment with three columns: ID, StartTime, and EndTime. The ID column is an INT type primary key, and both the StartTime and EndTime columns are of type DATETIME2(3), representing a precision of milliseconds. We insert a record representing the start and end times of a scientific experiment and query all data in the table using a SELECT statement. The result is as follows:

Example 2

The following example uses the DATETIME2 data type to store historical prices of a stock:

CREATE TABLE StockPrice (
    ID INT PRIMARY KEY,
    Price MONEY NOT NULL,
    PriceDate DATETIME2(0) NOT NULL
);

INSERT INTO StockPrice (ID, Price, PriceDate)
VALUES (1, 123.45, '2022-03-10 09:30:00'),
       (2, 124.56, '2022-03-11 09:30:00'),
       (3, 125.67, '2022-03-12 09:30:00');

SELECT * FROM StockPrice;

In the above example, we create a table called StockPrice with three columns: ID, Price, and PriceDate. The ID column is an integer type primary key, the Price column is a currency type column, and the PriceDate column is a DATETIME2 type column with a precision of seconds (0 decimal places).

Next, we inserted three rows of data into the StockPrice table, corresponding to different dates and prices. Finally, we used the SELECT statement to query all the data in the StockPrice table. The query result contains three rows of data, each of which includes the values of the ID, Price, and PriceDate columns. Through this example, we can see that the DATETIME2 data type can conveniently store data with time information and support high-precision time information.

Conclusion

The DATETIME2 data type provides high-precision date and time storage and calculation functions and can be used in scenarios that require accurate time calculations. However, due to its large storage space, it should be used according to the actual situation. At the same time, it is important to pay attention to compatibility issues between different databases and applications.