How to return only the Date from a SQL Server DateTime datatype ?

To extract only the date portion from a DATETIME column in SQL Server, you can use the following methods:

1. CAST to DATE (SQL Server 2008+)

Converts the DATETIME value to the DATE type (removes the time portion):

SELECT CAST(YourDateTimeColumn AS DATE) AS DateOnly
FROM YourTable;

• Result: A DATE type (e.g., 2023-10-05).
• Note: Requires SQL Server 2008 or later.

2. CONVERT with Style Code

Converts the DATETIME to a formatted string (no time):

-- Style 23: 'YYYY-MM-DD'
SELECT CONVERT(VARCHAR, YourDateTimeColumn, 23) AS DateString
FROM YourTable;

-- Style 101: 'MM/DD/YYYY'
SELECT CONVERT(VARCHAR, YourDateTimeColumn, 101) AS DateString
FROM YourTable;

• Result: A string representation of the date (e.g., 2023-10-05).

3. DATEADD and DATEDIFF (Works in Older Versions)

Truncates the time by resetting it to 00:00:00.000:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, YourDateTimeColumn), 0) AS DateWithoutTime
FROM YourTable;

• Result: A DATETIME type with time set to 00:00:00.000 (e.g., 2023-10-05 00:00:00.000).
• Works in: All SQL Server versions.

4. FLOOR with Float Conversion

Uses the numeric representation of DATETIME to strip time:

SELECT CAST(FLOOR(CAST(YourDateTimeColumn AS FLOAT)) AS DATETIME) AS DatePart
FROM YourTable;

• Result: A DATETIME type with time set to 00:00:00.000.

5. FORMAT (SQL Server 2012+)

Formats the DATETIME as a string (flexible but slower):

SELECT FORMAT(YourDateTimeColumn, 'yyyy-MM-dd') AS FormattedDate
FROM YourTable;

• Result: A string (e.g., 2023-10-05).

Example Usage

Assume a table Orders with a OrderDate column of type DATETIME:

-- Using CAST (recommended for modern SQL Server)
SELECT OrderID, CAST(OrderDate AS DATE) AS OrderDateOnly
FROM Orders;

-- Using CONVERT (for string output)
SELECT OrderID, CONVERT(VARCHAR, OrderDate, 23) AS OrderDateString
FROM Orders;

Key Takeaways

• Use CAST AS DATE for simplicity and performance (SQL Server 2008+).
• Use CONVERT with style codes if you need a formatted string.
• Use DATEADD/DATEDIFF for compatibility with older SQL Server versions.
• Avoid FORMAT for large datasets due to performance overhead.