SQL Server Date Data Types

As a senior database developer, I often receive requirements to utilize the date data type in SQL Server. In this comprehensive article, I’ll walk you through everything you need to know about SQL Server date data types.

SQL Server Date Data Types

Why Date Data Types in SQL Server

Before diving into the specifics, let’s understand why choosing the correct date data type is critical. Using the appropriate date data types ensures:

• Accurate storage of temporal data
• Efficient querying and indexing
• Proper validation of date values
• Simplified date arithmetic operations

SQL Server provides several specialized date and time data types to handle various temporal data requirements. Let’s explore each one in detail.

Core SQL Server Date and Time Data Types

DATE

The DATE data type stores date values only (year, month, day) without any time component.

Key characteristics:

• Format: YYYY-MM-DD
• Range: January 1, 1 CE through December 31, 9999 CE
• Storage size: 3 bytes
• Best for: When you only need to track calendar dates (such as birthdays, holidays, etc.)

Example

We can use the SQL query below to create the Employees table with the HireDate column, which has the Date data type.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    HireDate DATE
);

Now, we can execute the following query to insert a record into the table we created above.

INSERT INTO Employees VALUES (1, 'John Smith', '2025-05-15');

After executing the above query, I got the expected output as shown in the screenshot below.

TIME

The TIME data type stores time values only without any date component.

Key characteristics:

• Format: hh:mm.nnnnnnn
• Range: 00:00:00.0000000 through 23:59:59.9999999
• Storage size: 3-5 bytes (depending on precision)
• Best for: When you only need to track time values (shift times, store hours, etc.)

Example

Here we are creating the ShiftSchedule table with different columns of TIME data types.

CREATE TABLE ShiftSchedule (
    ShiftID INT PRIMARY KEY,
    ShiftName VARCHAR(50),
    StartTime TIME,
    EndTime TIME
);

INSERT INTO ShiftSchedule VALUES (1, 'Morning Shift', '08:00:00', '16:00:00');

After executing the above query, the table CustomerOrders has been created successfully as shown in the screenshot below.

After executing the above query, the data was inserted into the table as per the screenshot below.

DATETIME

The DATETIME data type stores both date and time values.

Key characteristics:

• Format: YYYY-MM-DD hh:mm.nnn
• Range: January 1, 1753 through December 31, 9999
• Precision: Rounded to increments of .000, .003, or .007 seconds
• Storage size: 8 bytes
• Best for: Legacy applications that require backward compatibility

While DATETIME is widely used, Microsoft recommends using DATETIME2 for new development due to its improved precision and wider date range.

DATETIME2

DATETIME2 is an extension of the DATETIME type with a larger date range, higher fractional seconds precision, and user-defined precision.

Key characteristics:

• Format: YYYY-MM-DD hh:mm.nnnnnnn
• Range: January 1, 1 CE through December 31, 9999 CE
• Precision: Up to 7 decimal places (100ns)
• Storage size: 6-8 bytes (depending on precision)
• Best for: Most modern applications that need to store both date and time

Example

Here we are creating the CustomerOrders table with different columns of the DATETIME2 data type.

CREATE TABLE CustomerOrders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDateTime DATETIME2(3),
    ShipDateTime DATETIME2(3)
);

After executing the above query, the table CustomerOrders has been created successfully as shown in the screenshot below.

INSERT INTO CustomerOrders VALUES (1001, 505, '2025-05-15 14:30:15.123', '2025-06-16 09:45:22.456');

After executing the above query, the data got inserted to the table as per the screenshot below.

SMALLDATETIME

A compact date and time data type with reduced range and precision.

Key characteristics:

• Format: YYYY-MM-DD hh:mm
• Range: January 1, 1900 through June 6, 2079
• Precision: Rounded to the nearest minute
• Storage size: 4 bytes
• Best for: Applications where storage space is a concern and minute precision is sufficient

DATETIMEOFFSET

This data type stores date and time values with time zone awareness.

Key characteristics:

• Format: YYYY-MM-DD hh:mm.nnnnnnn [+|-]hh
• Range: January 1, 1 CE through December 31, 9999 CE
• Precision: Up to 7 decimal places (100ns)
• Storage size: 8-10 bytes (depending on precision)
• Best for: Global applications that need to track time zone information

Example

CREATE TABLE FlightSchedule (
    FlightID INT PRIMARY KEY,
    FlightNumber VARCHAR(10),
    DepartureTime DATETIMEOFFSET(3),
    ArrivalTime DATETIMEOFFSET(3)
);

After executing the above query, the FlightSchedule table was created successfully, as shown in the screenshot below.

After executing the above query, the data got inserted to the table as per the screenshot below.

Comparison of SQL Server Date and Time Data Types

Here’s a handy comparison table of all the date and time data types in SQL Server:

Working with Date Data Types in SQL Server

Now that we understand the available date data types, let’s explore how to work with them effectively.

Date Literals and Conversions

SQL Server supports various formats for date literals, but I recommend using the ISO 8601 format (YYYY-MM-DD) as it’s unambiguous and works regardless of your server’s

Language settings:

-- ISO 8601 format (recommended)
Select * from Employees WHERE hiredate = '2025-05-15';

After executing the above query, I got the expected output as per the screenshot below.

-- With explicit conversion
SELECT * FROM Employees WHERE hiredate = CONVERT(DATE, '2025-05-15');

After executing the above query, I got the expected output as per the screenshot below.

Converting Between Date Data Types

You can convert between different date data types using the CAST or CONVERT functions:

Example

We can execute the following query to convert DATE to DATETIME2.

-- Convert DATE to DATETIME2
SELECT CAST(HireDate AS DATETIME2) FROM Employees;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Example

We can execute the following query to convert a string to a DATE.

-- Convert string to DATE
SELECT CONVERT(DATE, '2023-06-15') AS ConvertedDate;

After executing the above query, I got the expected output as per the screenshot below.

Example

We can execute the following query to convert a DATETIME to a DATE.

-- Convert DATETIME to DATE (removes time portion)
SELECT CAST(OrderDateTime AS DATE) FROM CustomerOrders;

After executing the above query, I got the expected output as per the screenshot below.

Date and Time Functions

SQL Server provides a rich set of date and time functions that make working with temporal data easier:

Getting Current Date and Time

-- Current date (no time component)
SELECT GETDATE() AS CurrentDateTime,
       CAST(GETDATE() AS DATE) AS CurrentDate,
       CURRENT_TIMESTAMP AS CurrentTimestamp,
       SYSDATETIME() AS SystemDateTime,
       SYSUTCDATETIME() AS UTCDateTime,
       SYSDATETIMEOFFSET() AS DateTimeWithOffset;

After executing the above query, I got the expected output as shown in the screenshot below.

Extracting Parts of Dates

DECLARE @SampleDate DATETIME2 = '2025-06-15 14:30:25.1234567';

SELECT YEAR(@SampleDate) AS TheYear,
       MONTH(@SampleDate) AS TheMonth,
       DAY(@SampleDate) AS TheDay,
       DATEPART(QUARTER, @SampleDate) AS TheQuarter,
       DATEPART(WEEK, @SampleDate) AS WeekOfYear,
       DATEPART(WEEKDAY, @SampleDate) AS DayOfWeek,
       DATEPART(HOUR, @SampleDate) AS TheHour,
       DATEPART(MINUTE, @SampleDate) AS TheMinute,
       DATEPART(SECOND, @SampleDate) AS TheSecond,
       DATEPART(MILLISECOND, @SampleDate) AS TheMillisecond;

After executing the above query, I got the expected output as shown in the screenshot below.

Best Practices for Working with Date Data Types

Based on my experience working with SQL Server in numerous projects, here are some best practices I recommend:

1. Choose the correct data type for your needs:
   • Use DATE when you only need dates without time
   • Use DATETIME2 instead of DATETIME for new development
   • Use DATETIMEOFFSET when time zone information is important
2. Always use four-digit years to avoid Y2K-like problems.
3. Use the ISO 8601 format (YYYY-MM-DD) for date literals to prevent ambiguity and issues related to language and regional settings.
4. Include time zone handling in your application design if your system operates across multiple time zones.
5. Consider storage requirements when selecting date types, especially for very large tables.
6. Use appropriate indexing on date columns that are frequently used in WHERE clauses.

Conclusion

As a senior SQL developer, it is crucial to know the usage of the SQL Server date data types as mentioned in this article.

You may also like the following articles.

• SQL Server Date vs DateTime
• SQL Server Date Formatting
• SQL Server Subtract Days From Date
• SQL Server Add Time To Date
• SQL Server Extract Year From Date
• SQL Server Insert Date

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.