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(datetim e, CIFExpDate) < CURRENT_TIMESTA MP
ORDER BY CONVERT(datetim e, 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(CIFExpDa te) = 1 then
CONVERT(datetim e, CIFExpDate) else NULL) < CURRENT_TIMESTA MP
ORDER BY convert(case isdate(CIFExpDa te) = 1 then
CONVERT(datetim e, 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