SQL Server 2005 compare dates

Question

I'm having a problem with some T-SQL in a SP on SQLServer 2005 comparing dates. I'm running the stored procedure from c# with ADO.Net and passing a the native c# datetime datatype(could this be my issues as I know the ranges are slightly different). I do the following to compare 2 DateTime values in my SP.

CREATE PROCEDURE [dbo].[spGetLikelyMatchedIndividuals_v1]
    @ID BIGINT = NULL,
    @DOB DATETIME = NULL, ...

WHERE ISNULL(CONVERT(CHAR(8),Ind.[DateOfBirth],112),'') = ISNULL(CONVERT(CHAR(8),@DOB,112),'')

This works fine in most cases but from some reason will fail with some Datetime. This is one datetime value that fails:

1925-07-04

Does anyone have anyidea why this may fail? Also what is the best way to compare two date values without the time component?

Answer 1

You just want to compare the date component? You could compare

FLOOR(CAST(x.[SomeDate] as float)) = FLOOR(CAST(@SomeDate as float))

A lot less string work, and should do the job. Even better; create a 1-day range and use that...

DECLARE @from datetime, @to datetime

SET @from = CAST(FLOOR(CAST(@SomeDate as float)) as datetime)
SET @to = DATEADD(day, 1, @from)

...
WHERE x.[SomeDate] >= @from AND x.[SomeDate] < @to

Answer 2

If you're comparing dates on SQL-Server, investigate the DateDiff function.

You can compare two dates quite easily and specify the granularity, eg. to the nearest day or hour or minute or whatever.

In your example, one of your values is a datetime, so convert the other to that type using the Convert function.

Answer 3

Better yet, don't do any logic against the table as this will prevent your index from being used.

Let your front end app handle the ensuring that the @DOB variable is in the correct format.

Answer 4

Seems like your date compare is correct. It may be other logic that is causing this issue. Perhaps you should paste in more of your stored procedure to find the likely problem.