How to Convert DATETIME to DATE in SQL Server (2024)

Do you have a DATETIME value in SQL Server that you want to convert to a DATE, or only get the DATE component?

You can do that in SQL Server. There are several ways to do it. I’ll show you how in this article.

Scenario

You’ve got a value that’s stored as DATETIME, which could be in a database table or from another source. You then want to convert it to DATE, or somehow only show the date part of the datetime.

For example, to get the current date and time you can use:

SELECT GETDATE();

Result:

2022-09-02 19:21:00.700

This shows the current date and time, down to fractional seconds.

But what if you only want to see the date (day, month, and year) and none of the time component?

There are many ways you can do this.

SQL Convert Datetime to Date

You can convert a DATETIME to a DATE using the CONVERT function. The syntax for this is CONVERT (datetime, format).

For example, to convert the current date and time into just a date:

SELECT CONVERT(date, GETDATE());

Result:

2022-09-02

This shows the date only and no time.

What other methods exist?

There are different ways you can use CAST, DATEDIFF, FLOOR, and CONVERT to get this result.

Performance Test Setup

The best way to see what the other methods are and how they perform is to run a quick performance test. I got this idea from a StackOverflow question from 10 years ago, and thought I’d update it using a newer SQL Server version.

For this test, I’m using SQL Server on AWS RDS. Here’s the specific version:

SELECT @@VERSION;

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) – 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Now let’s set up some test data. This script does a few things:

• Creates a table called AllDay;
• Inserts a lot of records
• Executes a procedure to find the space used.

The records inserted are based on the DateAdd function. The +20 means it adds 20 milliseconds to the previous value, so one value for every 20 milliseconds. This results in about 4 million rows for the table.

It took me about 20 minutes to run on AWS (and I think it stopped before it finished). But it has quite a lot of data in order for the performance to be measured.

CREATE TABLE AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);DECLARE @d datetime;SET @d = DateDiff(Day, 0, GetDate());INSERT AllDay SELECT @d;WHILE @@ROWCOUNT != 0INSERT AllDay SELECT * FROM (SELECT Tm =DateAdd(ms, ( SELECT Max(DateDiff(ms, @d, Tm)) FROM AllDay ) + 20, Tm)FROM AllDay) XWHERE Tm < DateAdd(Day, 1, @d);EXEC sp_spaceused AllDay;

Here’s the result of sp_spaceused:

Now we have the test data, let’s see the different methods.

While you’re here, if you want an easy-to-use list of the main features in SQL Server, get my SQL Cheat Sheet here:

Convert Datetime to Date: Methods and Performance Test Results

This script will run several different methods of converting a DATETIME to a DATE:

CONVERT (date, Tm)CAST(Tm - 0.50000004 AS int)DATEDIFF(DAY, 0, Tm)FLOOR(CAST(Tm as float))CONVERT(VARCHAR(8), Tm, 112)CONVERT(CHAR(8), Tm, 112)CONVERT(VARCHAR(10), Tm, 101)

The script for running these is:

set statistics time on;GODECLARE @d datetime;SELECT @d = CONVERT(date, Tm) FROM AllDay;SELECT @d = CAST(Tm - 0.50000004 AS int) FROM AllDay;SELECT @d = DATEDIFF(DAY, 0, Tm) FROM AllDay;SELECT @d = FLOOR(CAST(Tm as float)) FROM AllDay;SELECT @d = CONVERT(VARCHAR(8), Tm, 112) FROM AllDay;SELECT @d = CONVERT(CHAR(8), Tm, 112) FROM AllDay;SELECT @d = CONVERT(VARCHAR(10), Tm, 101) FROM AllDay;GOset statistics time off;

Here are the results of the different methods of converting DATETIME to DATE:

There are a few things to notice here.

The fastest method on approximately 4 million rows is CONVERT(date, tm). This also seems the simplest to explain to others and to read, as it’s converting a value using the CONVERT function.

CAST is a little slower. Using a value of -0.50000004 and casting it to INT was an old method tested in 2007 and uses this “magic number”. It’s hard to understand why this may work, but it does give you the result that’s needed.

DATEDIFF is almost as fast as CONVERT in CPU time and a little faster in elapsed time. However, I believe it’s a little harder to understand. This is probably the second-best option to use.

Using FLOOR and CAST with a float value is an option, however I”m a bit cautious when attempting to use floats. Perhaps it’s OK as it’s being rounded using FLOOR.

There are three methods to convert to a text value, either VARCHAR or CHAR. These are around the same speed as each other, with format 101 being a little slower. Format 101 equals “mm/dd/yyyy” and format 112 equals “yyyymmdd”, both of which are date-only formats without time.

Conclusion

There are several methods to convert a DATETIME to a DATE in SQL Server. The fastest (and most readable in my opinion) is to use CONVERT(date, yourvalue), but there are several others.

While you’re here, if you want an easy-to-use list of the main features in SQL Server, get my SQL Cheat Sheet here: