Date Conversion

[tman24m]

I use DTS to import data from our AS400 servers. When the date fields are imported, they come in as a decimal data type and look like 20020315. I want to convert them to a datetime data type. I've tried using Transformation to convert but am having no luck.

I have some jobs that are daily and it sure would be nice if I could automate this.

thanks

 

[Guest_imported]

You may use following conversions:
SELECT CAST( CAST(20020315 AS VARCHAR) AS DATETIME ) (which display 2002-03-15 00:00:00.000)
or
SELECT CAST( CAST( AS400DateField AS VARCHAR) AS DATETIME )

Succes !

 

[tman24m]

that doesn't seem to work for me. I tried it in query analyzer and got the following

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Any other suggestions?

 

[bperry]

Anything in this you can use?.......
I tried this in QA and it seems to work fine.

bp

declare @myInput int
set @myInput = 20011201

declare @myDate datetime
set @myDate = convert(datetime,cast(@myInput as varchar))

select @mydate

 

[mukund]

Hi tman24m,
Assuming that from AS400 the dates are coming in yyyymmdd format you can convert them in to date format by using

Select Convert(datetime, str(20020315))

this will convert your decimal date in 20020315 format to a datetime format.

I tried this on SQL 7 & SQL 2000 & this works fine.

Hope this helps,

Mukund.