datetime2 Data Type

Overview

The datetime2 data type is used to store date and time values. It is a superset of the older datetime and smalldatetime data types. datetime2 supports a larger date range, higher precision, and is ANSI SQL compliant.

Key Features of datetime2:

• Date Range: January 1, 0001 through December 31, 9999.
• Time Precision: Up to 100 nanoseconds, configurable by fractional seconds precision.
• Storage Size: Varies depending on the specified precision, typically 6 to 8 bytes.
• Time Zone: Does not store time zone information.

Using datetime2 is generally recommended over datetime and smalldatetime for new development due to its enhanced capabilities and compliance with SQL standards.

Syntax

When declaring a datetime2 variable or column, you can specify the fractional seconds precision.

datetime2(fractional_seconds_precision)

Parameters:

• fractional_seconds_precision: Optional. Specifies the number of digits to the right of the decimal point in the seconds value. It can be an integer from 0 to 7. The default precision is 7.

Storage Requirements:

Behavior

The datetime2 data type stores date and time values independently of the server's time zone. This means that if you insert a date and time value on a server in one time zone, it will be stored exactly as provided, regardless of the client's time zone.

Date and Time Components:

A datetime2 value consists of the following components:

• Year
• Month
• Day
• Hour
• Minute
• Second
• Fractional Seconds

Implicit Conversion:

datetime2 can be implicitly converted to and from various string literals and other date/time data types. However, explicit conversion is often preferred for clarity and to avoid unexpected behavior.

Performance Considerations

While datetime2 offers significant advantages, consider the following for performance:

• Precision: Use the lowest precision necessary to save storage space and potentially improve performance, especially in large tables.
• Storage: Higher precision requires more storage, which can impact I/O operations and memory usage.
• Indexing: Ensure that appropriate indexes are created on datetime2 columns used in queries for optimal performance.

Examples

Declaring a Variable with Default Precision:

DECLARE @MyDateTime2 datetime2;
SET @MyDateTime2 = '2023-10-27 10:30:15.1234567';
PRINT @MyDateTime2;

Declaring a Variable with Specific Precision:

DECLARE @MyDateTime2WithPrecision datetime2(3); -- Precision of 3 decimal places for seconds
SET @MyDateTime2WithPrecision = '2023-10-27 10:30:15.123';
PRINT @MyDateTime2WithPrecision;

Inserting into a Table:

CREATE TABLE SampleDateTime2 (
    EventID INT PRIMARY KEY IDENTITY(1,1),
    EventName VARCHAR(100),
    EventTimestamp datetime2(6) -- Stores up to 6 decimal places for seconds
);

INSERT INTO SampleDateTime2 (EventName, EventTimestamp)
VALUES ('System Startup', GETDATE()); -- GETDATE() returns current datetime

INSERT INTO SampleDateTime2 (EventName, EventTimestamp)
VALUES ('User Login', '2023-10-27 14:05:00.123456');

SELECT * FROM SampleDateTime2;

Related Topics