Get Date From Timestamp SQL Server

As a SQL developer, I often received the requirement to retrieve a date from SQL Server. In this comprehensive guide, I’ll walk you through multiple methods for extracting date components from SQL Server timestamps with practical examples.

Get Date From Timestamp SQL Server

Before diving into extraction methods, it’s essential to clarify what we mean by “timestamp” in SQL Server, as there’s often confusion around this term.

In SQL Server, there are several datetime-related data types:

• datetime: Stores date and time with accuracy to 3.33 milliseconds
• datetime2: Enhanced version with larger date range and better precision (up to 100ns)
• smalldatetime: Compact version storing dates from 1900 to 2079 with 1-minute precision
• date: Stores date only (no time component)
• time: Stores time only (no date component)
• datetimeoffset: Stores date and time with time zone awareness

Confusingly, SQL Server also has a timestamp (now called rowversion) data type, but it doesn’t store date or time information—it’s used for version control of rows.

For this article, I’ll focus on extracting the date component from datetime-related fields that contain both date and time information.

Approach 1: Using CONVERT to Extract Date from Timestamp

The CONVERT function is my go-to method when working with clients who need a straightforward, widely compatible solution for extracting dates from timestamps.

-- Basic date extraction using CONVERT
SELECT 
    Order_Date,
    CONVERT(date, Order_Date) AS OrderDate
FROM OrdersNew

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

Customizing Date Formats with CONVERT

When working with a retail client in Dallas last year, they required dates to be formatted in the US standard MM/DD/YYYY format for their reporting system. CONVERT makes this simple by adding a style parameter:

-- Extract date with US-specific formatting (MM/DD/YYYY)
SELECT 
    Order_Date,
    CONVERT(varchar, Order_Date, 101) AS FormattedOrderDate
FROM OrdersNew

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

Here’s a reference table of the most useful style codes I use regularly:

Approach 2: Using the CAST function

The CAST function provides a cleaner, ANSI SQL-standard approach for extracting dates.

SELECT 
    Order_Date,
    CAST(Order_Date AS date) AS OrderDate
FROM OrdersNew

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

This approach is beneficial when:

• You want to maintain ANSI SQL compatibility
• You need the result as a date data type (not a string)
• You’re writing code that might be ported to other database systems

Approach 3: Using DATEADD and DATEDIFF for Date Truncation

A less common but highly effective approach is using the DATEADD and DATEDIFF functions together to truncate the time portion. I’ve found this method particularly useful when working with older versions of SQL Server (2005 and earlier) that don’t support the date data type.

-- Truncate time component using DATEADD and DATEDIFF
SELECT 
    Order_Date,
    DATEADD(day, DATEDIFF(day, 0, Order_Date), 0) AS OrderDate
FROM OrdersNew

After executing the above query, I received the expected output.

This method works by:

1. Finding the number of days between January 1, 1900 (represented by 0) and your timestamp using DATEDIFF
2. Adding that number of days back to January 1, 1900 using DATEADD
3. The result is a datetime value with the time portion set to 00:00:00

Method 4: Using the FORMAT Function (SQL Server 2012 and Later)

For newer SQL Server versions (2012+), the FORMAT function provides a more intuitive way to extract and format dates.

SELECT 
    Order_Date,
    FORMAT(Order_Date, 'yyyy-MM-dd') AS OrderDate
FROM OrdersNew

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

The FORMAT function is handy when:

• You need a specific output format
• You want to leverage .NET formatting strings
• Code readability is a priority

However, it’s worth noting that in my performance testing, FORMAT is typically slower than CONVERT or CAST, especially for large datasets.

Approach 5: Extracting Individual Date Components

Sometimes, you need to extract specific components of a date, rather than the entire date, from the timestamp. We can execute the below query for that purpose.

SELECT 
    Order_Date,
    YEAR(Order_Date) AS OrderYear,
    MONTH(Order_Date) AS OrderMonth,
    DAY(Order_Date) AS OrderDay
FROM OrdersNew

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

Conclusion

Below are the key recommendations and conclusion:

• For simple date extraction: Use CAST(timestamp_column AS date) for the best balance of readability and performance
• For formatted output: Use CONVERT(varchar, timestamp_column, format_code) with the appropriate format code
• For maximum flexibility: Use the FORMAT() function, but be aware of potential performance implications
• For performance-critical systems: Consider adding computed columns with appropriate indexes
• For cross-platform compatibility: Stick with CAST and avoid SQL Server-specific functions

By using these approaches appropriately to your specific scenario, you can ensure optimal performance when extracting dates from timestamps in SQL Server.

You may also like the following articles.

• SQL Server Add 1 Day To Date
• SQL Server Date Format 101
• SQL Server Date Formatting
• SQL Server Subtract Days From 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.