Date & Time Data Types
SQL Server provides different date and time data types to store dates, times and date-time values. These data types are commonly used for birth dates, joining dates, order dates, transaction times and other time-related information.
What are Date & Time Data Types?
Date and time data types are used to store calendar dates, time values or both date and time information in SQL Server.
SQL Server provides different options depending on whether an application needs only a date, only a time, or both date and time with different levels of precision.
Common Date & Time Data Types
| Data Type | Description | Example |
|---|---|---|
| DATE | Stores only the date. | 2026-10-02 |
| TIME | Stores only the time. | 14:30:00 |
| DATETIME | Stores both date and time. | 2026-10-02 14:30:00 |
| DATETIME2 | Stores date and time with greater precision than DATETIME. | 2026-10-02 14:30:00.1234567 |
| SMALLDATETIME | Stores date and time with minute-level precision. | 2026-10-02 14:30 |
| DATETIMEOFFSET | Stores date and time together with a time-zone offset. | 2026-10-02 14:30:00 +05:30 |
DATE
The DATE data type stores only the calendar date. It does not store the time portion.
It is useful for values such as date of birth, joining date, admission date and holiday date.
CREATE TABLE Employees
(
EmployeeId INT,
EmployeeName VARCHAR(100),
JoiningDate DATE
);
TIME
The TIME data type stores only the time portion of a value.
CREATE TABLE ClassSchedule
(
ClassName VARCHAR(100),
StartTime TIME,
EndTime TIME
);
DATETIME
The DATETIME data type stores both date and time information.
It is commonly used for order dates, login times and transaction records.
CREATE TABLE Orders
(
OrderId INT,
OrderDate DATETIME
);
DATETIME2
DATETIME2 stores date and time values with greater fractional-seconds precision than DATETIME.
CREATE TABLE Transactions
(
TransactionId INT,
TransactionDate DATETIME2
);
DATETIMEOFFSET
The DATETIMEOFFSET data type stores date and time information together with a time-zone offset.
It is useful when applications work with users or systems located in different time zones.
CREATE TABLE GlobalTransactions
(
TransactionId INT,
TransactionTime DATETIMEOFFSET
);
Example: Using Date & Time Data Types
The following example creates an Employees table using different date and time data types.
CREATE TABLE Employees
(
EmployeeId INT,
EmployeeName VARCHAR(100),
BirthDate DATE,
JoiningTime TIME,
JoiningDateTime DATETIME
);
Insert Date & Time Values
INSERT INTO Employees
(
EmployeeId,
EmployeeName,
BirthDate,
JoiningTime,
JoiningDateTime
)
VALUES
(
1,
'Rahul',
'1995-05-15',
'09:30:00',
'2026-10-02 09:30:00'
);
Output
| EmployeeId | EmployeeName | BirthDate | JoiningTime | JoiningDateTime |
|---|---|---|---|---|
| 1 | Rahul | 1995-05-15 | 09:30:00 | 2026-10-02 09:30:00 |
The output demonstrates how SQL Server stores date-only, time-only and combined date-time values.
Get Current Date and Time
SQL Server provides built-in functions that can be used to retrieve the current date and time.
SELECT GETDATE();
SELECT SYSDATETIME();
SELECT CAST(GETDATE() AS DATE);
Output
GETDATE()
-----------------------
2026-10-02 15:07:25
SYSDATETIME()
-----------------------
2026-10-02 15:07:25.1234567
CAST(GETDATE() AS DATE)
-----------------------
2026-10-02
The exact output depends on the current date and time of the SQL Server environment.
Choosing the Correct Date & Time Data Type
| Requirement | Recommended Data Type |
|---|---|
| Only date | DATE |
| Only time | TIME |
| Date and time | DATETIME2 |
| Time zone offset required | DATETIMEOFFSET |
Important
Always select a date and time data type according to the actual requirement of the application. If only a date is required, there is no need to store a time value.
Summary
In this section, we learned about SQL Server date and time data types including DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME and DATETIMEOFFSET. We also learned how to create tables, insert date and time values and retrieve the current date and time.