Re: Handling dates from IMAGE/ALLBASE databases (128 Views)
Reply
Advisor
Mickey MacDonagh
Posts: 21
Registered: ‎12-03-1999
Message 1 of 7 (128 Views)

Handling dates from IMAGE/ALLBASE databases

Hi,

I am using Microsoft DTS to extract data from HP3000 databases. The dates come across as A21125. What's the best way to handle this string and convert it to a date in SLQ Server?

Regards,

Mickey
Please use plain text.
Advisor
Adam Dorritie
Posts: 12
Registered: ‎03-11-2001
Message 2 of 7 (128 Views)

Re: Handling dates from IMAGE/ALLBASE databases

Hello Mickey,

If I remember correctly, MANMAN formats its dates like this:
00-99 == 1900-1999
A0-J9 == 2000-2099

If I'm right, your date "A21125" is therefore the same as "20021125" or Nov 25, 2002.

You'll have to add to the DTS script to parse this value prior to trying to add it into the SQL Server database.

Regards,

Adam Dorritie
Easy Does It Technologies LLC
(702) 807-2144
http://www.editcorp.com
Please use plain text.
Advisor
Mickey MacDonagh
Posts: 21
Registered: ‎12-03-1999
Message 3 of 7 (128 Views)

Re: Handling dates from IMAGE/ALLBASE databases

Hi Adam,

Thanks for your reply. I'm not so sure how to add to the script to do the transformation but if I could get a handle on the syntax it would be helpful.

I have used the following to generate a varchar(19):

'200' + substring(date_shipped,2,1)+ '-' + substring(date_shipped,3,2)+ '-' + substring(date_shipped,5,2) + ' ' + '00:00:00'

This gives me :

2002-10-03 00:00:00

from A21003. Where it fails is in the converting datetime from character string when I use the CONVERT(datetime, string expr above)

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

I know it's probably slightly off topic but I'm just looking for the best way to handle those dates from the 3000.

Thanks,

Mickey
Please use plain text.
Advisor
Ken Vickers
Posts: 13
Registered: ‎06-19-1997
Message 4 of 7 (128 Views)

Re: Handling dates from IMAGE/ALLBASE databases

Assuming that you are using ODBC via ImageSQL to access the data then you can map the dates in ImageSQL see communicator 6.5
http://docs.hp.com/mpeix/onlinedocs/30216-90291/30216-90291.html

e.g.
UPDATE TYPE IN TABLE2.MYDATE FORMAT=DT35,'000000','999999' TO DATE
Please use plain text.
Advisor
Adam Dorritie
Posts: 12
Registered: ‎03-11-2001
Message 5 of 7 (128 Views)

Re: Handling dates from IMAGE/ALLBASE databases

Mickey,

Here is a script that I created using two sample databases, you should be able to modify it for use in your environment.

The most important piece is the function convert_date. As long as you add this verbatim to your transformation script, you should be able to convert_date (source_field) anywhere you need to do so.

Regards,

Adam Dorritie
Easy Does It Technologies LLC
(702) 807-2144
http://www.editcorp.com

Please use plain text.
Advisor
Mickey MacDonagh
Posts: 21
Registered: ‎12-03-1999
Message 6 of 7 (128 Views)

Re: Handling dates from IMAGE/ALLBASE databases

Thanks Adam,

I'll give it a shot.

Regards,

Mickey
Please use plain text.
Occasional Visitor
Adel Bawazir
Posts: 1
Registered: ‎02-26-2007
Message 7 of 7 (128 Views)

Re: Handling dates from IMAGE/ALLBASE databases

Hi Mickey


I have problem with extract data from hp3000
I am using odbc32 to link hp with sql server

but I have this message

""odbclinksa does not allow to multiple threads""

Please help me if you have solution for it

Regards.

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation