SQL Server date string search guidelines - comparing dates

------------

-- SQL Server date string search guidelines - comparing dates

------------

-- Date equal search

DECLARE @Date1 datetime, @Date2 datetime, @Date3 datetime

SET @Date1 = '2012-01-01'

SET @Date2 = '2012-01-01 00:00:00.000'

SET @Date3 = '2012-01-01 11:00'

SELECT @Date1, @Date2, @Date3

-- Date-only @Date1 is translated to datetime

-- 2012-01-01 00:00:00.000    2012-01-01 00:00:00.000  2012-01-01 11:00:00.000

-- The following is a datetime comparison, not a date-only comparison

IF (@Date1 = @Date2) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'

-- EQUAL

-- Equal test fails because time parts are different

IF (@Date1 = @Date3) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'

-- NOT EQUAL

-- The string date implicitly converted to datetime for the equal test

IF ('2012-01-01' = @Date3) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'

-- NOT EQUAL

-- Safe way to search for a specific date

SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader

WHERE '2004/02/01' = CONVERT(varchar, OrderDate,111)

-- 244

-- Equivalent to

SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader

WHERE OrderDate BETWEEN '2004/02/01  00:00:00.000' AND '2004/02/01 23:59:59.997'

-- 244

-- Safe way to search for a specific date range

SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader

WHERE CONVERT(varchar, OrderDate,111) BETWEEN '2004/02/01' AND '2004/02/14'

-- 1059

-- Equivalent to

SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader

WHERE OrderDate BETWEEN '2004/02/01 00:00:00.000' AND '2004/02/14 23:59:59.997'

-- 1059

SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader

WHERE OrderDate >= '2004/02/01 00:00:00.000'

  AND OrderDate <  '2004/02/15 00:00:00.000'

-- 1059 

------------