In SQL Server, date and time data types are used to store date and time information. These types can represent just the date, just the time, or both, with various levels of precision. Here's a detailed explanation of each date and time data type, with examples and key differences:
Date and Time Data Types
1. Date
- Description: Stores a date without the time.
- Range: January 1, 0001 to December 31, 9999.
- Storage Size: 3 bytes.
- Example: Suitable for storing birthdates, hire dates, or any dates where the time is not needed.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
BirthDate DATE
);
2. TIME
- Description: Stores a time of day without the date.
- Range: 00:00:00.0000000 to 23:59:59.9999999.
- Storage Size: 3 to 5 bytes, depending on the precision.
- Example: Suitable for storing business hours, shift start times, or any times where the date is not needed.
CREATE TABLE WorkShifts (
ShiftID INT PRIMARY KEY,
ShiftName NVARCHAR(50),
StartTime TIME(7),
EndTime TIME(7)
);
3. DATETIME
- Description: Stores both date and time.
- Range: January 1, 1753 to December 31, 9999.
- Storage Size: 8 bytes.
- Example: Suitable for storing timestamps, event logs, or any data that requires both date and time.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDateTime DATETIME
);
Upgrade Your Skills - Enroll in Our SQL Server Developer Course at TrainingHub.io!
4. DATETIME2
- Description: Stores both date and time with more precision than DATETIME.
- Range: January 1, 0001 to December 31, 9999.
- Storage Size: 6 to 8 bytes, depending on the precision.
- Example: Suitable for applications that require high precision in date and time.
CREATE TABLE Transactions (
TransactionID INT PRIMARY KEY,
TransactionDateTime DATETIME2(7)
);
5. SMALLDATETIME
- Description: Stores both date and time, with less precision than DATETIME.
- Range: January 1, 1900 to June 6, 2079.
- Storage Size: 4 bytes.
- Example: Suitable for applications that require date and time but do not need high precision.
CREATE TABLE Appointments (
AppointmentID INT PRIMARY KEY,
AppointmentDateTime SMALLDATETIME
);
6. DATETIMEOFFSET
- Description: Stores date and time with time zone awareness.
- Range: January 1, 0001 to December 31, 9999.
- Storage Size: 10 bytes.
- Example: Suitable for applications that need to store dates and times with the time zone offset.
CREATE TABLE GlobalEvents (
EventID INT PRIMARY KEY,
EventDateTime DATETIMEOFFSET(7)
);
7. TIMESTAMP (or ROWVERSION)
- Description: Stores a unique binary number that changes every time a row is modified.
- Range: A binary value that is unique within a database.
- Storage Size: 8 bytes.
- Example: Suitable for tracking row modifications in tables.
- Note: TIMESTAMP is not a date or time data type but is used for version-stamping table rows.
CREATE TABLE Documents (
DocumentID INT PRIMARY KEY,
DocumentContent NVARCHAR(MAX),
RowVersion TIMESTAMP
);
Experience the best in data education with TrainingHub.io’s Data Analyst courses in Toronto, designed to meet current industry standards.
Key Differences
- Range and Precision: Different types offer different ranges and levels of precision. For example, DATETIME is less precise than DATETIME2, and SMALLDATETIME has a smaller range than both.
- Storage Size: Varies from 3 bytes for DATE to 10 bytes for DATETIMEOFFSET.
- Time Zone Awareness: Only DATETIMEOFFSET includes time zone offset information.
- Usage Scenarios: Choose the type based on the required precision, range, and whether time zone awareness is needed.
Examples
Using DATE
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
BirthDate DATE
);
- BirthDate stores just the date.
Using TIME
CREATE TABLE WorkShifts (
ShiftID INT PRIMARY KEY,
ShiftName NVARCHAR(50),
StartTime TIME(7),
EndTime TIME(7)
);
- StartTime and EndTime store the time of day with high precision.
Using DATETIME
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDateTime DATETIME
);
- OrderDateTime stores both date and time.
Using DATETIME2
CREATE TABLE Transactions (
TransactionID INT PRIMARY KEY,
TransactionDateTime DATETIME2(7)
);
- TransactionDateTime stores both date and time with higher precision than DATETIME.
Using SMALLDATETIME
CREATE TABLE Appointments (
AppointmentID INT PRIMARY KEY,
AppointmentDateTime SMALLDATETIME
);
- AppointmentDateTime stores both date and time with less precision.
Using DATETIMEOFFSET
CREATE TABLE GlobalEvents (
EventID INT PRIMARY KEY,
EventDateTime DATETIMEOFFSET(7)
);
- EventDateTime stores date, time, and time zone offset.
Using TIMESTAMP (ROWVERSION)
CREATE TABLE Documents (
DocumentID INT PRIMARY KEY,
DocumentContent NVARCHAR(MAX),
RowVersion TIMESTAMP
);
- RowVersion stores a unique binary number for each row modification, useful for concurrency control.
Take the Next Step - Join Our SQL Server Developer Course! TrainingHub.io stands out as the best IT training institute in Canada due to our innovative teaching methods and state-of-the-art facilities.
Suggested blogs
Recent blogs
