Comparing dates without time... 1

[BFreshour]

I give up. Maybe I'm just stupid but I can't figure out how to get my database to compare two datetime fields without taking in account the time that's attached to them.

For example, I have an activation period and when stored in the database it keeps the time. It would look something like this: 2002-09-23 23:27:16.000

I have a stored procedure that runs every day and I want it to look at this date and if it's today's date (no matter what the time) I want it to update the table. I have all the code written, but I can't get the comparison correct.

Right now I have:

Date_Eligible < getdate()

Which doesn't work unless the time is later than that on the 'Date_Eligible' field.

In summary, if the 'Date_Eligible' is 2002-09-23 23:27:16.000, I want it to run the update even if getdate() is 2002-09-23 04:27:16.000.

I hope this makes sense.

 

[rt63]

convert(varchar(8),Date_Eligible,112) < convert(varchar(8),getdate())



RT

 

[rt63]

Sorry... missed out the format code for getdate()


convert(varchar(8),Date_Eligible,112) < convert(varchar(8),getdate(),112)




RT

 

[wengyan]

DECLARE @Date_Eligible datetime
SELECT @Date_Eligible ='2002-09-24 23:27:16.000'

SELECT GETDATE() NOW,@Date_Eligible Date_Eligible,'TRUE' RESULT WHERE DATEPART(year,@Date_Eligible)=DATEPART(year,GETDATE())
AND DATEPART(month,@Date_Eligible)=DATEPART(month,GETDATE())
AND DATEPART(day,@Date_Eligible)=DATEPART(day,GETDATE())

NOW Date_Eligible RESULT
------------------------------------------------------ ------------------------------------------------------ ------
2002-09-24 14:40:19.010 2002-09-24 23:27:16.000 TRUE WENG YAN