Need help - Error - The conversion of a char data type to a datetime data type

The Mighty Dubs

Hi,

Im trying to convert a varchar to a datetime and am getting the following error.

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

One of the problems is that the data in the fields are in the following formats

Dec 31 2008 12:00AM
02/17/2009
20/02/2009
<null>

I need to somehow convert all these to
20/02/2009 format

Im using MSSQL 2000 btw

Any help will be most appreciated.

Thanks

Ginger

You can use an ISNULL statement to get you out of the whole NULL issue.

Your best bet it is run it through an ASPX file and adjust the logic, otherwse run a stored proc with a cursor and see what happens

ronivek

You can also use isdate() to check if a varchar is suitable for conversion in the first place.

Andrew76

Hi there,

To add to the other replies, the following code converts your sample values to DATETIME:

DECLARE @Str VARCHAR(100)
-- Comment/uncomment to test diff values.
SET @Str = 'Dec 31 2008 12:00AM'
--SET @Str = '02/17/2009'
--SET @Str = '20/02/2009'
--SET @Str = NULL

SELECT @Str [Var], 
CASE WHEN ISDATE(@Str) = 1 THEN CAST(@Str AS DATETIME)
ELSE CONVERT(DATETIME,@Str,103) 
END [Date]

I needed the convert 103 for the 20/02/2009 value (probably due to my sql language settings). Hope this is of some help.

Andrew.