A year ago I wrote post about SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice where I have discussed two different methods of getting datepart from datetime.
Method 1:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
Method 2:
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
I have summarized my post suggesting that either method works fine and I prefer to use Method 2. However, with additional tests and looking at SQL Server internals very carefully, I want to suggest that Method 1 is better in terms of performance. While running on GETDATE() both of the above functions are equally fast and efficient. However, when above query is ran on very large data table performance different is visible.
For testing purpose I have ran few queries where I am getting same result.
Run Following query 10 times with client statistics on ( Shift+Alter+S) in SSMS.
USE AdventureWorks
GO
SELECT DATEADD(d, 0, DATEDIFF(d, 0, StartDate))
FROM Production.WorkOrder
GO
Now run another query 10 times with client statistics on.
USE AdventureWorks
GO
SELECT CONVERT(VARCHAR(10),StartDate,111)
FROM Production.WorkOrder
GO
While looking at the average of statistics in it is clear that first query with DATEADD is much more efficient.
Let us compare both of the above script together and see the client statistics.
While comparing Numbers of Bytes sent across client and server as well client processing time, and total execution time in all case DATEADD is better. I suggest all my readers to use DATEADD when only DATEPART is required.
If you are using SQL Server 2008 it has new DATE datatype which does not have time part. I suggest to use DATE datatype or TIME datatype for users of SQL Server 2008. Read more SQL SERVER – 2008 – New DataTypes DATE and TIME.
If you want your date in any other format refer the UDF SQL SERVER – Get Date Time in Any Format – UDF – User Defined Functions.
Reference : Pinal Dave (https://blog.sqlauthority.com)
