Newbie question on DTS DateTime Data

[EITech]

I'm just getting started with SQL Server. I'm using the DTS wizard to set up an import from a Hewlett-Packard database. The H-P has a timestamp field that is not importing correctly -- I think it is importing in unicode? I searched the BOL and MS KB but didn't find anything that seemed to address this. Is there a way to get this field to convert to a time stamp that can be read? Right now SQL Server is bringing it in as a char(6) field. Thanks!

 

[fluteplr]

Dates are frequently stored in databases in wierd proprietory ways. Without knowing exactly how your hp database is storing the date, we probably can't help you.

Is it posible to have the hp database output the date as a character field in some format that sql server will recognize like 'yyyy-mm-dd'?

 

[EITech]

OK, I was afraid of that. I'll see if I can find out more about the H-P formats. Thanks for info.

 

[tlbroadbent]

You can do an ActiveX transformation in DTS.

If you use the Import/Export Wizard, when you get to the screen where you select tables and view, click on the Transform button. Select the Transformations tab. Select "Transform information as it is copied to the destination." You'll see the VB scripting language transformation script. You can VB scripting language (or javascript if you prefer) to convert the HP date string to a string that SQL Server will recognize. The code may be as simple as the following transformation.

DTSDestination("dt_Col&quot = CDate(DTSSource("Col006&quot)

There are other functions available in VB script to transform the data if needed - FormatDateTime or string maniplation functions like Left, Mid, etc. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin