There are a few ways to remove the time from a datetime in SQL. This article describes two of them.
I am deliberately ignoring ways to do this using TEXT functions – these are slow and to be avoided for this type of task.
Two ways to remove the time from a datetime in SQL
One way to remove the time is with a formula. The second way is to change the data type.
Remove the time from a SQL datetime using a formula
This is the code to remove the time:
DATEADD(dd,0,DATEDIFF(dd,0,DateToRemoveTime))
I’ll explain how this works as it was quite confusing initially.
Part 1: DATEDIFF(dd,0,DateToRemoveTime)
What this is doing is finding the number of whole days (without time) from the beginning of time (0) to the date you want to remove the time (DateToRemoveTime).
Part 2: DATEADD(dd,0,Part1)
This is taking the number of whole days since time began which was calculated in Part 1 and adding this to the beginning of time (0).
As an example let’s say it’s 10.03 on the 14 Jan in Year 0. In SQL date format this would appear as something like 0000:01:14 10:03:00
Part 1 would see how many whole days have passed since the beginning of time, 0, and the 14 Jan in Year 0.
This is 14 days.
Part 2 would add these 14 days to the beginning of time – which would give the result 0000:01:14 00:00:00 – which is the date without the time.
Remove the time from a SQL datetime using the DATE datatype
In SQL 2008 there’s another way to remove the time. Use the DATE datatype.
The syntax is very simple:
CAST(DateToRemoveTime as DATE)
The best method to remove the time from a SQL datetime
The next obvious question is which is the best method to use to remove the time portion from a date.
To test which method performs the best I ran both options on a dataset of 1.9m rows of data.
The DATE datatype returned the records in 15m 35s = 935s
Using the DATEADD and DATEDIFF technique took 16m 50s = 1020s
In this example using the DATE datatype was 10% faster – which is significant.
Therefore, based on this test using the DATE datatype is the best way to remove the time from a datetime in SQL.
I’m also aware of techniques to remove the time using text functions. DO NOT USE THESE – they are not handled in an optimal way by the SQL engine. They will be slower than the above techniques.