Comparing DateTime Column in SQL Server

Introduction: This article addresses the following issues:

1) Time also stores in DateTime column therefore Time is also required while comparing
2) How to extract only Date (excluding the Time) from DateTime column

Level: Beginner

Knowledge Required:

• T-SQL
• SQL Server 2005/2008/2012

Description:
While working with Database we usually face an issue where we need to compare the given Date with SQL Server DateTime column. For example:

SELECT * FROM Orders WHERE Order_Date = '5-May-2008'

As we can see in above example we are comparing our date i.e. '5-May-2008' with the DateTime column Order_Date. But it is NOT sure that all the Rows of 5-May-2008 will be returned. This is because Time also stores here. So if we look into the Table we will find that,

Order_Date = '5-May-2008 10:30'
Order_Date = '5-May-2008 11:00'
Order_Date = '5-May-2008 14:00'

So when we give '5-May-2008' to SQL Server then it automatically converts it into:

Order_Date = '5-May-2008 12:00'

Therefore this date will NOT be equal to any of the dates above.

There are several techniques to handle this issue, I will discuss some here:

1) Compare all the 3 parts (day, month, year)
2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function
3) Use Date Range

1) Compare all the 3 parts:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   Day(Order_Date) = Day(@Given_Date) AND
        Month(Order_Date) = Month(@Given_Date) AND
        Year(Order_Date) = Year(@Given_Date);

2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   CONVERT(varchar(100), Order_Date, 112) = CONVERT(varchar(100), @Given_Date, 112);

Note that CONVERT function will work as:

Print CONVERT(varchar(100), CAST('5-May-2008' AS DateTime), 112);

Output:

20080505

But we have limitation i.e. cannot use '<' and '>' operators here.

3) Use Date Range
Example:

DECLARE @Given_Date DateTime;
DECLARE @Start_Date DateTime;
DECLARE @End_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';
SET @Start_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) As DateTime);
SET @End_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) + ' 23:59:59' As DateTime);

SELECT *
FROM Order
WHERE Order_Date Between @Start_Date AND @End_Date;

In my opinion the last example is the fastest, since in all the previous examples SQL Server has to perform some extraction/conversion each time while extracting the Rows. But in the last method SQL Server will convert the Given Date only once, and in SELECT statement each time it is comparing the Dates, which is obviously faster than comparing a Date after converting it into VarChar or comparing each part of Date. Also in previous 2 methods we have limitation i.e. we cannot use the Range.


The following table summarizes these six data types format, range, accuracy, and storage size in bytes, and is taken from the Date and Time Data Types and Functions technical documentation.

Data type	Format	Range	Accuracy	Storage size (bytes)
time	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 through 23:59:59.9999999	100 nanoseconds	3 to 5
date	YYYY-MM-DD	0001-01-01 through 9999-12-31	1 day	3
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 through 2079-06-06	1 minute	4
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 through 9999-12-31	0.00333 second	8
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999	100 nanoseconds	6 to 8
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)	100 nanoseconds	8 to 10

 

Note: The time, datetime2, and datetimeoffset data types' storage spaces are between a range because when you use the data type you can specify the precision.