CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
LateBloomer
LateBloomer

Reputation: 97

ISDATE Validation issue 1753

I have a table RpParams with columns called ParamName and ParamValue. They both are of type varchar(200).

A stored procedure started failing earlier this month.

I have narrowed it down to

SELECT 
   ParamName,
   ParamValue =
        CASE 
           WHEN ISDATE(ParamValue) = 1 
             THEN CAST(CAST(RTRIM(LTRIM(ParamValue) AS DATETIME) AS VARCHAR(50))
           ELSE ParamValue
        END
...

The issue has occurred as soon as the value in ParamValue was 1753 or greater. I do know that 1753 - 9999 as considered valid dates, so ISDATE is now returning 1 and converting the 1753 value to a date.

How can I validate the field but avoid the >=1753 issue I am having?

This is on SQL Server 2005

Upvotes: 1

Views: 277

Answers (1)

bwperrin
bwperrin

Reputation: 692

Since strings that look like a valid year convert to Jan 1 of that year when converting to date, we want to make sure the varchar doesn't also look like a number. You want:

CASE WHEN ISDATE(ParamValue) = 1 AND ISNUMERIC(ParamValue) = 0

Upvotes: 1

Related Questions