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

Extracting Only the Date from SQL Server DateTime

Learn various methods to retrieve just the date component from a SQL Server DateTime or DateTime2 column, ensuring consistent and accurate results for your applications.

SQL Server's DATETIME and DATETIME2 data types store both date and time information. However, there are many scenarios where you only need the date part, such as for grouping daily records, filtering by date without considering time, or displaying dates in a user interface. This article explores several robust methods to achieve this, catering to different SQL Server versions and performance considerations.

Understanding SQL Server Date/Time Types

Before diving into extraction methods, it's crucial to understand the DATETIME and DATETIME2 types. DATETIME stores dates from January 1, 1753, to December 31, 9999, with an accuracy of 3.33 milliseconds. DATETIME2, introduced in SQL Server 2008, offers a larger date range (0001-01-01 to 9999-12-31) and higher precision (up to 100 nanoseconds), making it generally preferred for new development. Both types inherently include a time component, even if it's '00:00:00.000'.

erDiagram
    "Table A" ||--o{ "Table B" : "has"
    "Table A" {
        int ID PK
        varchar(50) Name
        datetime CreatedDateTime
    }
    "Table B" {
        int ID PK
        int A_ID FK
        datetime EventDateTime
    }
    "Date Only View" {
        int A_ID PK
        date CreatedDate
    }

Conceptual ER Diagram showing a DateTime column and a derived Date Only View

Method 1: Using CAST or CONVERT to DATE (SQL Server 2008+)

The simplest and most recommended method for SQL Server 2008 and later is to CAST or CONVERT the DATETIME or DATETIME2 value to the DATE data type. The DATE data type stores only the date part (year, month, day) without any time component. This method is clean, efficient, and semantically correct.

DECLARE @FullDateTime DATETIME = '2023-10-26 14:35:00.123';

-- Using CAST
SELECT CAST(@FullDateTime AS DATE) AS DateOnly_CAST;

-- Using CONVERT
SELECT CONVERT(DATE, @FullDateTime) AS DateOnly_CONVERT;

Casting and Converting a DATETIME to DATE

Method 2: Using CONVERT with Style Codes (Older SQL Server Versions)

For older versions of SQL Server (pre-2008) or when you need specific string formatting, CONVERT with a style code can be used. Style code 101 (mm/dd/yyyy) or 103 (dd/mm/yyyy) are common for converting to a string, and then you can CAST that string back to DATETIME or DATE if needed. However, if the goal is a DATE type, the previous method is superior. If the goal is a string representation of the date, this method is useful.

DECLARE @FullDateTime DATETIME = '2023-10-26 14:35:00.123';

-- Convert to string with style 101 (mm/dd/yyyy) and then cast back to DATETIME
SELECT CAST(CONVERT(VARCHAR(10), @FullDateTime, 101) AS DATETIME) AS DateOnly_Style101;

-- Convert to string with style 103 (dd/mm/yyyy) and then cast back to DATETIME
SELECT CAST(CONVERT(VARCHAR(10), @FullDateTime, 103) AS DATETIME) AS DateOnly_Style103;

-- Convert to string with style 120 (yyyy-mm-dd hh:mi:ss) and then take only the date part
SELECT CONVERT(VARCHAR(10), @FullDateTime, 120) AS DateOnly_String;

Extracting date using CONVERT with style codes

Method 3: Mathematical Approach (Less Recommended)

Before the DATE data type was available, a common trick was to use mathematical operations to strip the time component. This involved converting the DATETIME to a FLOAT (where the integer part represents the date and the fractional part represents the time), truncating the fractional part, and then converting back to DATETIME. While it works, it's less readable and generally slower than CAST to DATE.

DECLARE @FullDateTime DATETIME = '2023-10-26 14:35:00.123';

SELECT CAST(FLOOR(CAST(@FullDateTime AS FLOAT)) AS DATETIME) AS DateOnly_Math;

Mathematical approach to extract date

Choosing the Right Method

The best method depends on your SQL Server version and specific requirements:

• SQL Server 2008 and later: Always use CAST(YourColumn AS DATE). It's the most efficient, readable, and semantically correct approach.
• Older SQL Server versions (pre-2008): If you need a DATETIME type with time set to midnight, CONVERT(DATETIME, CONVERT(VARCHAR(10), YourColumn, 101)) or similar style codes are often used. If you only need a string representation, CONVERT(VARCHAR(10), YourColumn, 120) is a good choice.

Always test the performance of your chosen method, especially on large tables, to ensure it meets your application's needs.