Remove Time Part from Datetime in SQL Server

Using the DATEPART function:

• Extract the date part from the datetime value using DATEPART('d', datetime_column).
• Create a new datetime value by combining the extracted date part with a time value of midnight (00:00:00).

SELECT DATEADD(day, DATEDIFF(day, 0, datetime_column), 0) AS date_only
FROM your_table;

• Cast the datetime value to a date data type, which automatically removes the time part.

SELECT CAST(datetime_column AS DATE) AS date_only
FROM your_table;

• Convert the datetime value to a date data type using the CONVERT function.

SELECT CONVERT(DATE, datetime_column) AS date_only
FROM your_table;

Using the DATEADD and DATEDIFF functions:

• Calculate the difference between the datetime value and midnight in days.
• Add the calculated difference to midnight to get the date-only value.

SELECT DATEADD(day, DATEDIFF(day, 0, datetime_column), 0) AS date_only
FROM your_table;

Using the TRUNCATE function (SQL Server 2012 and later):

• Truncate the datetime value to the nearest day using the TRUNCATE function.

SELECT TRUNCATE(datetime_column, DAY) AS date_only
FROM your_table;

Choosing the best approach:

• For most scenarios, the CAST or CONVERT functions are the simplest and most efficient options.
• If you need more flexibility or control over the date formatting, the DATEPART or DATEADD/DATEDIFF methods can be used.
• The TRUNCATE function is a more concise option available in SQL Server 2012 and later.

Example:

Assuming you have a table named Orders with a OrderDate column of type datetime, you can remove the time part using any of the methods above:

SELECT OrderID, CAST(OrderDate AS DATE) AS OrderDateOnly
FROM Orders;

Understanding the Example Codes

Scenario: Let's assume we have a table named Orders with a column OrderDate of type datetime. We want to extract only the date part without the time.

Method 1: Using CAST

This is the simplest and often the most efficient method.

SELECT OrderID, CAST(OrderDate AS DATE) AS OrderDateOnly
FROM Orders;

• Breakdown:
  • CAST(OrderDate AS DATE): Converts the datetime value in the OrderDate column to a DATE data type, which inherently does not contain a time component.
  • OrderDateOnly: An alias for the calculated date-only value.

Similar to CAST, CONVERT can also be used.

SELECT OrderID, CONVERT(DATE, OrderDate) AS OrderDateOnly
FROM Orders;

• Breakdown:

Method 3: Using DATEPART and DATEADD

This method is more verbose but offers more flexibility.

SELECT OrderID, DATEADD(day, DATEDIFF(day, 0, OrderDate), 0) AS OrderDateOnly
FROM Orders;

• Breakdown:
  • DATEDIFF(day, 0, OrderDate): Calculates the difference in days between the OrderDate and the base date (1900-01-01).
  • DATEADD(day, DATEDIFF(day, 0, OrderDate), 0): Adds the calculated difference back to the base date, effectively setting the time to midnight.

Method 4: Using TRUNCATE (SQL Server 2012+)

This method is concise and straightforward.

SELECT OrderID, TRUNCATE(OrderDate, DAY) AS OrderDateOnly
FROM Orders;

• Breakdown:

• Simplicity and Efficiency: For most scenarios, CAST or CONVERT are recommended due to their simplicity and performance.
• Flexibility: If you need more control over date manipulation, DATEPART and DATEADD can be useful.
• Conciseness: TRUNCATE is a good option if you're using SQL Server 2012 or later.

Alternative Methods for Removing the Time Part in SQL Server

While the methods discussed earlier are generally the most efficient and straightforward, here are some alternative approaches that might be useful in specific scenarios:

Using a Subquery

You can use a subquery to extract the date part and then join it back to the main table:

SELECT 
    OrderID, 
    DATEADD(day, DATEDIFF(day, 0, OrderDate), 0) AS OrderDateOnly
FROM 
    Orders;

Creating a User-Defined Function

For complex date manipulations or repeated usage, you can create a user-defined function:

CREATE FUNCTION dbo.RemoveTimePart (@datetime DATETIME)
RETURNS DATE
AS
BEGIN
    RETURN DATEADD(day, DATEDIFF(day, 0, @datetime), 0);
END
GO

SELECT 
    OrderID, 
    dbo.RemoveTimePart(OrderDate) AS OrderDateOnly
FROM 
    Orders;

Using a Computed Column

If you frequently need the date-only value, you can create a computed column in your table:

ALTER TABLE Orders
ADD OrderDateOnly AS DATEADD(day, DATEDIFF(day, 0, OrderDate), 0) PERSISTED;

Using a CTE (Common Table Expression)

For more complex queries involving date manipulations, a CTE can be useful:

WITH OrdersWithDateOnly AS (
    SELECT 
        OrderID, 
        OrderDate, 
        DATEADD(day, DATEDIFF(day, 0, OrderDate), 0) AS OrderDateOnly
    FROM 
        Orders
)
SELECT 
    OrderID, 
    OrderDateOnly
FROM 
    OrdersWithDateOnly;

Using a View

If you need to frequently query the date-only value, creating a view can simplify your queries:

CREATE VIEW dbo.OrdersWithDateOnly AS
SELECT 
    OrderID, 
    OrderDate, 
    DATEADD(day, DATEDIFF(day, 0, OrderDate), 0) AS OrderDateOnly
FROM 
    Orders;

• Simplicity and Efficiency: CAST and CONVERT are generally the most efficient and straightforward options.
• Flexibility: User-defined functions and CTEs offer more flexibility for complex date manipulations.
• Performance: If you need to frequently query the date-only value, a computed column or view can improve performance.