conversion of a char data type to a datetime data type resulted in an 5

[DataNick]

To all,
I am trying to convert data in a varchar field to a datetime field in a temp table, so that I can perform MIN,MAX operations on the field.

I've tried CAST, CONVERT both without success:


SELECT DISTINCT strProviderID, CAST(StartDate AS datetime) AS StartDate
INTO ##BCR_05_temp2
FROM ##BCR_05_temp1

/*
Server: Msg 242, Level 16, State 3, Line 1
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.
*/


SELECT DISTINCT strProviderID, CONVERT(datetime,StartDate) AS StartDate
INTO ##BCR_05_temp2
FROM ##BCR_05_temp1
WHERE ##BCR_05_temp1.StartDate IS NOT NULL

/*
Server: Msg 242, Level 16, State 3, Line 1
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.
*/


The temp table ##BCR_05_temp1 has field values taken from a dbo.table whose StartDate field is a varchar.

Any ideas?

I'm kinda stumped on this one.

BTW: I got rid of any NULL values in the temp table & re-tried the operation with the same results.

Thanks!

Nick

 

[rac2]

The ISDATE(expression) may be useful here. You could use it to find rows with problematic StartDates in the source table and edit them to be in a valid format. Or you could use it in a CASE statement to check the VARCHAR value and only use the CAST if ISDATE = 1 otherwise plug some value or NULL.

 

[SQLBill]

Most likely you have dates like: 01/02/2006. What date is that? Is it Jan 2, 2006 or 1 Feb 2006? How is SQL Server supposed to know? Then when the date is 01/15/2006? Is that the 1st day of the 15th month of 2006? That's not valid.

You can tell SQL Server how to interpret dates by using SET DATEFORMAT. For example: SET DATEFORMAT DMY lets SQL Server know that the dates are in day, month, year order.

This combined with rac2's suggestion of using ISDATE() should help you solve your problem.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLSister]

also if you have character data you may need to trim the data to convert becasue of the extra spaces it puts in. ANd then there may be other values which are outright not dates such as 2/30/2006 or ASAP or 3/2005 (how would SQL know what day to put in here?)

So your first step is to find the records which don;t have valid dates and fix them or null them out. then you can convert. This is one reason why date data should never ever be stored as anything other than datetime data type.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[DataNick]

Hi everyone!

Thanks to all who responded.
I will be implementing the recommendations shortly.

And I know!, I don't know why this table, which is not 'Normalized' BTW, was created with varchars storing date data. Go figure!
Of course, I'm trying to clean up the mess to get a critical report out.

Thank you again rac2, SQLBill, and SQLSister!

Nick

 

[DataNick]

Thanks again to everyone!
Here's the final solution:

--Step1
--Make temp table1 from Business table
SELECT strID, FROMDate,ThruDate,Specialty
INTO ##tblBusiness_temp
FROM Business



--Step2
--Create temp table to modify date values stored as varchar
--reformat varchar to char(10)

SELECT strID, CONVERT (char(10),substring(FROMDate,6,2)+ '/' +
substring(FROMDate,5,2)+ '/' +
substring(FROMDate,1,4))
AS Start_Date
INTO ##tblBusiness_charTemp
FROM ##tblBusiness_temp
WHERE FROMDate IS NOT NULL

--Results show 10,000 out of 12,000 rows that are not NULL
--Many rows have invalid date values



--Step3: Query valid date values that are stored as varchars
--SELECT date values stored as varchars that are within a 'real' date format range
SELECT *
FROM ##tblBusiness_charTemp
WHERE substring(Start_Date,5,2)+ '/' +
substring(Start_Date,1,4) < '12'

--(5500 row(s) affected)


--Step4: Convert or Cast the char(10) field values to datetime


SELECT strID,
CASE
WHEN isdate(Start_Date) = 1
Then CONVERT(datetime,Start_Date)
End
FROM ##tblBusiness_charTemp
WHERE substring(Start_Date,5,2)+ '/' +
substring(Start_Date,1,4) < '12'