smalldatetime convert problem

I am converting a varchar(10) field to a smalldatetime in a stored procedure but have run into a problem. Some of the rows contain an invalid date. varchar(10) format is mm/dd/yyyy some rows are filled with 00/00/0000 however causing the convert to fail. how can I get around this? Updating the table is not an option. My convert is as follows:
...
WHERE CONVERT(datetime, CIFExpDate) < CURRENT_TIMESTAMP
ORDER BY CONVERT(datetime, CIFExpDate) DESC
...

Thanks for any help!

re: smalldatetime convert problem

use the CASE FUNCTION and the
use ISDATE FUNCTION


-- CK

re: smalldatetime convert problem

Ok Closer I got the select to work:

But when I add the WHERE clause it fails:
This is obviously because the field is only being temporarily updated, so I assigned the values from the Select to a temp variable (calling a convert in the WHERE fails because of my original problems of 00/00/0000)

Then I get the the following error:

re: smalldatetime convert problem

Try these clause:


WHERE convert(case isdate(CIFExpDate) = 1 then
CONVERT(datetime, CIFExpDate) else NULL) < CURRENT_TIMESTAMP
ORDER BY convert(case isdate(CIFExpDate) = 1 then
CONVERT(datetime, CIFExpDate) else NULL)

Watch out for the time part. If you need to disregard the timepart, use DATEDIFF instead of simple less than.

-- CK

re: smalldatetime convert problem

Thanks, that got me headed in the correct direction and I got it to work, here is the final code for anyone who might want to do something similar: