Date&Time issue!!?

Date%26amp; Time Issue?


Hi, In my one of the table where i have DateTime column formatted like (2007-07-21 00:00:08.893).


The problem is when i try to retreve data by ''


SELECT * from table where Rdate = getdate()-1''. It will give me results even we have rows for all the days i want. Should i have to Convert date to get date or anything else !!.


Anyidea !!


Actually i am trying to create a Store Proc which will run every Week and delete the records from the table older then 10 days.For this i choosse the date Time column..But when i simply use the Select or Delete


command it returns with 0 row.





Please let me know !!


Thanks..

Date%26amp;Time issue!!?
You did not say which flavor of SQL you are using so this idea may not work for you.





Assuming you are using SQL Server, you may have to strip the time component from your date-time values before doing your comparisons because





"getdate() -1" subtracts one day from the current date %26amp; time but does not change the time-of-day portion so you only find matches where the time-stamp portion matches exactly.





You probably want to remove the time portion of the date-times before comparing. Look into using the CONVERT function.





SELECT * from table where Rdate %26lt; cast (convert(varchar,GETDATE() -10,101)) as datetime;





NOTE: The "101" is a format code that you may have to change based on the format that getdate() provides.