How do you create a table with a column of type DATETIME2?
LeoRobs
Posted by LeoRobs
Last Updated: July 11, 2024
SQL DATETIME2
To create a table with a column of type DATETIME2 in SQL Server, you can use the CREATE TABLE statement. The DATETIME2 data type is a more precise and flexible version of the DATETIME type, allowing for larger date ranges and fractional seconds. Here’s an example SQL statement that creates a table with a DATETIME2 column: 
CREATE TABLE YourTableName (
    Id INT PRIMARY KEY,
    EventName NVARCHAR(100),
    EventDate DATETIME2(7)  -- the number in parentheses specifies the fractional seconds precision (0 to 7)
);
Explanation:
- YourTableName: Replace this with your desired table name. - Id: This is an integer column that serves as the primary key. - EventName: This is a string column (up to 100 characters) to store names of events. - EventDate: This is the column of type DATETIME2. The (7) indicates the precision for fractional seconds, which can range from 0 (no fractional seconds) to 7 (which can store up to 100 nanoseconds).
Other Considerations:
- If you omit the precision in the DATETIME2 declaration, it defaults to (7). - You can also include other constraints or indexes as needed in your table definition.
Example Query:
To create a table named Events with a DATETIME2 column, the SQL query would be: 
CREATE TABLE Events (
    Id INT PRIMARY KEY,
    EventName NVARCHAR(100),
    EventDate DATETIME2(3)
);
This creates an Events table with a DATETIME2 column that stores timestamps with up to 3 decimal places of fractional seconds.
Default Image