Out-of-range DATETIME Value...

[rheilman]

I've tried several things on this. I got the following error message...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

...on the following code

SELECT This_Date =
CASE
WHEN This_Month IS NOT NULL
AND This_Day IS NOT NULL
AND This_Month BETWEEN '01' AND '12'
AND This_Day BETWEEN '01' AND '31'
THEN CAST(LTRIM(RTRIM(This_Month))
+ '/'
+ LTRIM(RTRIM(This_Day))
+ '/'
+ LTRIM(RTRIM(This_Year))
AS DATETIME)
ELSE CASE
WHEN This_Month IS NULL
THEN CAST('12/31/1899' AS DATETIME)
ELSE CAST('12/31/2025' AS DATETIME)
END
END

...where

This_Month char(2)
This_Day char(2)
This_Year char(2)

All the "single digit" values in these fields do have a leading zero.

If This_Month is NULL, then all three values will be NULL.

On some records there are data values that fall outside the range of normal months and days.

At this point, I'm not checking for 2/31/95, but I think that the problem may lie in the way that SQL Server is parsing. I'm trying to trap the bad conditions (null or just bad data) in the first ELSE.

If you see a something more elegant than my brute force method, I humbly await enlightenment!

Thanks!
Ray <><

 

[rheilman]

Sorry to bother you. It is April 31. I'll have a better question next time.

Thanks!
Ray <><