SQL DateTime issue? 3

[chester27]

I have a table with several columns of all varchar data. We then run a SP to move the data to a new table, one of which has a datetime setting. If the varchar data is in format ddmmyyyy it get The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value when I try and do a convert insert?

Script

INSERT INTO [a].
(
[c],)
Select
CASE [Col010] WHEN null THEN null WHEN '' THEN null ELSE Convert(datetime, [Col010], 104) END,
from [c].[d]

Col010 is varchar in [a]. but datetime in [c].[d].

Any help please?

 

[bborissov]

Select CASE WHEN ISDATE([Col010]) = 0
                 THEN NULL
       ELSE CONVERT(datetime, [Col010], 104) END AS TestDate,
       [Col010]
from [c].[d]

And then check to see which values are not converted.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[chester27]

All my values are returned, date is like 26042006. If I change it to 20060426 then it works, or 26/04/2006 but the extract we get to upload is in the millions and all will have date like 26042006?

 

[gmmastros]

When you have a string of 8 numbers that you try to convert to datetime, SQL Server will ALWAYS interpret this as yyyymmdd. There is no getting around this. The trick here is to change your data so that it is in this format. You can use a combination of Left, Right, and SubString to do this.

Ex:

Declare @Temp Table(DateCol VarChar(8))

Insert Into @Temp Values('26042006')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)

Select DateCol,
       Case When Len(DateCol) = 8
            Then Convert(DateTime, Right(DateCol,4) + SubString(DateCol, 3, 2) + Left(DateCol, 2))
            Else NULL END
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chester27]

Quick additional question. All works now apart from if an invalid datetime is seen ie 31112008 (Nov only 30 days) script stops. Anyway of adding to script to just insert null in this case?

 

[djj55]

Try the ISDATE command like

Case When Len(DateCol) = 8 AND ISDATE(Right(DateCol,4) + SubString(DateCol, 3, 2) + Left(DateCol, 2)) = 1
            Then Convert(DateTime, Right(DateCol,4) + SubString(DateCol, 3, 2) + Left(DateCol, 2))
            Else NULL END

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[bborissov]

Then combine both answers

Select CASE WHEN ISDATE(Right(Col010,4) + SubString(Col010, 3, 2) + Left(Col010, 2)) = 0 OR 
                 Len(Col010) <> 8
                 THEN NULL
       ELSE 
            Convert(DateTime, Right(Col010,4) +
                              SubString(Col010, 3, 2) +
                              Left(Col010, 2)) END
from [c].[d]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[chester27]

Thanks all for your assistance.