smalldatetime convert problem

[dcharnigo]

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!

[ck9663]

use the CASE FUNCTION and the
use ISDATE FUNCTION


-- CK

[dcharnigo]

Ok Closer I got the select to work:

SELECT
    CIFExpDate = 
    CASE
        WHEN ISDATE(CIFExpDate) = 1 THEN CONVERT(datetime, CIFExpDate)
        ELSE CURRENT_TIMESTAMP
    END
FROM
    zCIFRecord
 

But when I add the WHERE clause it fails:

WHERE CIFExpDate < CURRENT_TIMESTAMP

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)

DECLARE @datestring datetime
SELECT
    @datestring = 
    CASE
        WHEN ISDATE(CIFExpDate) = 1 THEN CONVERT(datetime, CIFExpDate)
        ELSE CURRENT_TIMESTAMP
    END
FROM
    zCIFRecord
WHERE @datestring < CURRENT_TIMESTAMP
 

Then I get the the following error:

"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

[ck9663]

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

[dcharnigo]

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:

SELECT
    CIFPan,
    CIFMemNum,
    CIFLName,
    CIFFName,
    CIFInitial,
    CIFExpDate
FROM
    zCIFRecord
WHERE
    CASE 
        WHEN ISDATE(CIFExpDate) = 1 
        THEN CONVERT(datetime, CIFExpDate) 
        ELSE NULL
    END < CURRENT_TIMESTAMP
ORDER BY 
    CASE
        WHEN ISDATE(CIFExpDate) = 1 
        THEN CONVERT(datetime, CIFExpDate) 
        ELSE NULL
    END ASC
GO