What is best way to compare datetime column in sql Server
If you are a database developer or a data analyst you are most probably have to develop a script that will compare datetime column in sql server with another datetime value or datetime function like getdate(). There are a lot of ways to compare these values but what is the best way to do this?
To demonstrate this let us create a table on AdventureWorks database and create a report that will return orders made today.
USE AdventureWorks2012; GO --------------------------------------------------------------------- --Create test table and fill sample data from Sales.SalesOrderHeader --------------------------------------------------------------------- IF OBJECT_ID('SalesOrder') IS NULL SELECT SalesOrderID ,RevisionNumber ,OrderDate ,DueDate ,ShipDate ,[Status] ,OnlineOrderFlag ,SalesOrderNumber ,PurchaseOrderNumber ,AccountNumber ,CreditCardApprovalCode ,SubTotal INTO SalesOrder FROM Sales.SalesOrderHeader ELSE INSERT INTO SalesOrder SELECT RevisionNumber ,OrderDate ,DueDate ,ShipDate ,[Status] ,OnlineOrderFlag ,SalesOrderNumber ,PurchaseOrderNumber ,AccountNumber ,CreditCardApprovalCode ,SubTotal FROM Sales.SalesOrderHeader GO 10 --run the batch 10 times to get more sample data --------------------------------------------------------------------- --Create nonclusted index on OrderDate --------------------------------------------------------------------- CREATE NONCLUSTERED INDEX IX_SalesOrder_OrderDate ON dbo.SalesOrder (OrderDate ASC); GO --------------------------------------------------------------------- --Since advenute works only has old data --let us update few OrderDate with getdate() --------------------------------------------------------------------- update dbo.SalesOrder SET OrderDate= GETDATE() WHERE SalesOrderID > 59310; --Rebuild index-- ALTER INDEX IX_SalesOrder_OrderDate ON dbo.SalesOrder REBUILD; GO |
Okay now we are ready to do our comparison let us first start by comparing the OrderDate with getDate() by converting the date function to date first
--------------------------------------------------------------------- --Test1, Convert (GETDATE() TO DATE) and compare >= --------------------------------------------------------------------- SELECT SalesOrderID ,RevisionNumber ,OrderDate ,DueDate ,ShipDate ,[Status] ,OnlineOrderFlag ,SalesOrderNumber ,PurchaseOrderNumber ,AccountNumber ,CreditCardApprovalCode ,SubTotal FROM dbo.SalesOrder WHERE OrderDate >= Cast(GETDATE() AS DATE); |
As you can see in the above execution plan there is table scanned since we can’t technically have a matching value on the OrderDate and another value that has DATE data type doesn’t have same value and we can’t use our index on OrderDate.
Now let as convert both values and even use other cool feature of CONVERT() function to give it formatting and see what happens
--------------------------------------------------------------------- --Test2, Convert both GETDATE() and OrderDate --------------------------------------------------------------------- SELECT SalesOrderID ,RevisionNumber ,OrderDate ,DueDate ,ShipDate ,[Status] ,OnlineOrderFlag ,SalesOrderNumber ,PurchaseOrderNumber ,AccountNumber ,CreditCardApprovalCode ,SubTotal FROM dbo.SalesOrder WHERE CONVERT(VARCHAR, OrderDate ,112) = CONVERT(VARCHAR, GETDATE() ,112); |
Well at least here we are only scanning index but still not the best possible plan. As you could see above sql server even give a working on this plan.
--------------------------------------------------------------------- --Test3, Compare with begin and end date --------------------------------------------------------------------- SELECT SalesOrderID ,RevisionNumber ,OrderDate ,DueDate ,ShipDate ,[Status] ,OnlineOrderFlag ,SalesOrderNumber ,PurchaseOrderNumber ,AccountNumber ,CreditCardApprovalCode ,SubTotal FROM dbo.SalesOrder WHERE OrderDate >= Cast(GETDATE() AS DATE) AND OrderDate <= DateAdd(DAY,1,Cast(GETDATE() AS DATE)); |
This proves we have a query with the best execution plan. And the query will perform much better when we have more and more data in SalesOrder table.
