Optimal way to remove time from Sql Date Time Column

As there is a separate data type [ date ] in sql server 2008 and on wards but still many  databases are relying on sql server 2000 and sql server 2005 where data type is only datetime and in most of the cases we don’t require time so to remove time part from date developers use different techniques.
Let’s analyze two commonly use techniques to remove time part from date field and know which is better in terms of time.

Select Count(1) From tblNames

508860 records.

For test i have a table of tblNames with almost 508860 dummy records i believe every one should have one dummy table to test each query themselves.

Let’s analyze the two majorly used queries.

Dbcc Freeproccache;
Dbcc Dropcleanbuffers;

Set Statistics Time On;

Select Convert(DateTime, Convert(Varchar, dtCurrentDate, 103), 103)
From tblNames

Set Statistics Time Off;

Firstly Dbcc Freeproccache and Dbcc Dropcleanbuffers is use to clean the cache as this is a very important step before analyzing the result because sql server cache results so if we don’t clean the cache then our result will not be correct.
Convert to datetime is first analyzed as this is a very common technique used but is this the best ???
After running the query 10 times and then taking the average of this query it resulted in average

Cpu time = 531 ms

Now analyze another technique to remove the time part from date time column.

Dbcc Freeproccache;
Dbcc Dropcleanbuffers;

Set Statistics Time On;

Select DateAdd(Day, 0, DateDiff(day, 0, dtCurrentDate))
From tblNames

Set Statistics Time Off;

This query looks unusual on a first look but let’s understand what it does, in the query initially the number of  days difference is calculated between the minimum sql date and the given date.
Note ( 0 means minimum sql date)

Then the resultant number of days are added by 0 which result in removal of time part from date.
Note ( 0 means number of days int)
To understand this query important this is to understand the functions DateAdd and DateDiff.

DateDiff (Interval, StartDate , EndDate) and it returns Integer.
where as
DateAdd(Interval, Increment Value Integer, GivenDate)
and it returns datetime.

Now back to the execution cpu time of this query. though it looks complex to see this conversion but the resultant time is mighty fast and almost half the previous convert to varchar query.
The average execution time after 5 run is

— SQL Server Execution Time:
— CPU time = 266 ms

So always use
 Select DateAdd(Day, 0, DateDiff(day, 0, dtCurrentDate))
From tblNames 
When ever you need to remove time from date time field.

Advertisements