[billbos]

I'm converting 2 date fields that are stored as VARCHAR for comparison, but I receive this error when running error: 'Syntax error converting datetime from character string', because there are 'N/A' values stored in the field (even though I have this in the WHERE clause to ignore 'N/A'). Anyone have an idea of how to get around this? Here's my code:


SELECT TOP 10 *
FROM WorkDB..Sales
WHERE CAST(Billing_End_Date AS DATETIME) > CAST(Coverage_End_Date AS DATETIME)
AND CAST(Billing_End_Date AS DATETIME) < (SELECT GETDATE())
AND Billing_End_Date <> 'N/A'

[andy_p]

you can't cast Billing_End_Date into a DATETIME before you've checked to see if it's 'N/A'. Try this:

SELECT TOP 10 * FROM WorkDB..Sales
WHERE CASE Billing_End_Date <> 'N/A'
WHEN true THEN CAST(Billing_End_Date AS DATETIME) > CAST(Coverage_End_Date AS DATETIME) AND CAST(Billing_End_Date AS DATETIME) < (SELECT GETDATE())
ELSE false
END

[andy_p]

or better, this:
WHERE CASE
WHEN Billing_End_Date <> 'N/A' THEN CAST(Billing_End_Date AS DATETIME) > CAST(Coverage_End_Date AS DATETIME) AND ....

look at http://msdn.microsoft.com/library/de...ca-co_5t9v.asp