datetime function with no time component?

Hi All,

When I compare dates but I want to ignore the time within the datetime I find myself doing this:

CONVERT(int, CONVERT(char(8), @.MyDate, 112))

style 112 is yyyymmdd

int is very predictable for comparisons, and performs well too.

It works but it is not readable, especially if you have several of these expressions in the same WHERE clause or CASE stmt. I also tried a udf but that has its own reusability problems across dbs and projects.

Is there a cleaner way to do this with a system function?

Carl

If you just want to compare dates, ignoring times, you could use the datediff function:

WHERE datediff( day, MyFirstDate, MyOtherDateTime ) = 0

For example:

Code Snippet

SELECT
Match = CASE
WHEN datediff( day, '2007/07/07 08:45 AM', getdate() ) = 0
THEN 'Match -Same Day'
ELSE 'Bummer! -No Match'
END,
NoMatch = CASE
WHEN datediff( day, '2007/07/06 08:45 AM', getdate() ) = 0
THEN 'Same Day'
ELSE 'Different Day'
END

Match NoMatch
-- -
Match -Same Day Different Day

DATEDIFF(), using the 'day' parameter, verifies that the two values are the same date IF there is NO difference [ = 0 ].

|||

Thanks Arnie,

For = and != logic, this is cleaner.

Not much of an improvement in readability for >, < , !>, and !< type comparisons

Carl

|||

And not too good for performance either.

While using the datediff() process 'looks' good, or as you said, 'cleaner', performance, related to other methods, can be disasterous. It will require at 'best', a clustered index scan. Actually, unless there is an index on the datetime column, it has to scan the entire table -which is what a 'clustered index scan' really is.

Compare that with the second option, my preferred method, of using date values in the criteria.

Code Snippet

USE Northwind
GO

SELECT *
FROM Orders
WHERE datediff( day, OrderDate, '1996/08/27' ) = 0

SELECT *
FROM Orders
WHERE ( OrderDate >= '1996/08/27'
AND OrderDate < '1996/08/28'
)

If you examine the execution plans, you will notice the method using the datediff() takes 19 times as long to execute since it has to scan the entire table.