Converting String field to Time field

JezT · Post by **JezT** » Wed Sep 15, 2004 4:27 am

I am attemtping to convert a source value into a valid time field to be loaded into a DB2 table.

For example, in the source file, I am getting the following values:

Source File

2
5
126
2545

And they should appear as time fields as follows

Target File

00:00:02
00:00:05
00:01:26
00:25:45

So therefore, in my transformer I have added 0's to the front of the field to make them all 6 characters long and have then formatted it by adding a : after every 2 characters. However, when I try to load this into the DB2 table, I get the following error.

Attempt to convert String value "00:00:02" to Time type unsuccessful.

Any ideas ?

Steve@HSBC · Post by **Steve@HSBC** » Wed Sep 15, 2004 5:22 am

What is the format of the DB2 table column you are trying to load?

In Unix type:

Code: Select all

db2 describe TABLE_NAME

and look at the format of the field you are trying to load.

i am assuming it will have the following format:

Code: Select all

TIME                      3

If so i would do this to your time:

Code: Select all

oconv(Variable_of_Time, "MTS")

Think that should help / point you in the right direction.

JezT · Post by **JezT** » Wed Sep 15, 2004 6:59 am

I have tried the Oconv you suggested but this converts the value in the source file into a seconds from midnight value which is not what I require.

For example, 109 in the source file should appear as 00:01:09 for my conversion but comes out as 00:01:49 (which is 109 seconds after midnight).

Steve@HSBC · Post by **Steve@HSBC** » Wed Sep 15, 2004 7:23 am

try:

Code: Select all

oconv(Iconv(YOUR_VARIABLE, "MTS"), "MTS")

What i am thinking is that DS stores a time value in the output different than if you format the string.

i may be wrong. So we convet your genereated time to internal, then convert it back into DS time format to insert into DB.

Let me know

JezT · Post by **JezT** » Wed Sep 15, 2004 7:30 am

If I put in your code either before I add the leading 0's or after, the results are the same and are still incorrect.

For example, 2 or 000002 appears as 02:00:00
37 or 000037 appears as 13:00:00 !!

Steve@HSBC · Post by **Steve@HSBC** » Wed Sep 15, 2004 8:08 am

Code: Select all

oconv(iconv (left((Str("0", 6 - Len(DSLink3.all)):DSLink3.all), 2) : ":" : right ( (left ((Str("0", 6 - Len(DSLink3.all)):DSLink3.all), 4)),2) : ":" : right ((Str("0", 6 - Len(DSLink3.all)):DSLink3.all),2 ), "MTS" ), "MTS")

Try that

garthmac · Post by **garthmac** » Wed Sep 15, 2004 8:09 am

This should work as a routine:

Zeros = FMT(Arg1,"6'0'R")
Ans = Iconv(Oconv(Zeros[1,2]:":":Zeros[3,2]:":":Zeros[5,2],"MTS"),"MTS")

garthmac · Post by **garthmac** » Wed Sep 15, 2004 8:26 am

Sorry, this is cleaner:

Zeros = FMT(Arg1,"6'0'R")
Ans = Iconv(Zeros[1,2]:":":Zeros[3,2]:":":Zeros[5,2],"MTS")

ray.wurlod · Post by **ray.wurlod** » Wed Sep 15, 2004 4:26 pm

This is even cleaner. It uses a mask in the Fmt function to insert the colon characters.

Code: Select all

Ans = Iconv(Fmt(Arg1, "6'0'R##:##:##"), "MT")

JezT · Post by **JezT** » Thu Sep 16, 2004 2:11 am

I have tried all of the previous suggestions but none of them work.

With the 2 routines suggested, I have created and compiled them but when I test them, they come up with incorrect results.

Code: Select all

Zeros = FMT(Arg1,"6'0'R") 
Ans = Iconv(Zeros[1,2]:":":Zeros[3,2]:":":Zeros[5,2],"MTS")

This gave the following results:
2 gave 2 (no zeros or colons)
258 gave 178.

Code: Select all

Ans = Iconv(Fmt(Arg1, "6'0'R##:##:##"), "MT")

When I tested the above, it gave no output at all for any figures I put in.

Code: Select all

oconv(iconv (left((Str("0", 6 - Len(DSLink3.all)):DSLink3.all), 2) : ":" : right ( (left ((Str("0", 6 - Len(DSLink3.all)):DSLink3.all), 4)),2) : ":" : right ((Str("0", 6 - Len(DSLink3.all)):DSLink3.all),2 ), "MTS" ), "MTS")

When I tried the above on my source values, it didn't populate the table and came back with the following error:

Value treated as NULL
Attempt to convert String value "00:00:02" to Time type unsuccessful

So it seems to have converted the source value into the correct format but it still won't populate the table.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 16, 2004 2:25 am

So what do you want to see? More to the point, what is DB2 expecting?

2 is interpreted as 00:00:02 in time context which, when converted to internal format, is 2 seconds after midnight.

258 is interpreted as 00:02:58 in time contect which, when converted to internal format, is 178 seconds after midnight.

You could try it without the Iconv, in which case

Code: Select all

Fmt(Arg1,"R%%:%%:%%")

gives a human-readable time.

garthmac · Post by **garthmac** » Thu Sep 16, 2004 2:49 am

I created a test job for this problem after testing my logic in a routine. I simply had a text file which contained the numbers in your example, applied my routine to them in a transformer, and then output to a DB2 table containing one field of type TIME. You must output the data in the DS internal date format to be able to populate the TIME field. I achieved the desired results.

JezT · Post by **JezT** » Thu Sep 16, 2004 3:44 am

Apologies !! Was getting myself confused. Forgot that testing the routine would output the time in the internal format and not the HH:MM:SS format.

Have added your routine Garth and it works fine.

Cheers !!!

:D

JezT · Post by **JezT** » Thu Sep 16, 2004 3:44 am

Apologies !! Was getting myself confused. Forgot that testing the routine would output the time in the internal format and not the HH:MM:SS format.

Have added your routine Garth and it works fine.

Cheers !!!

:D

garthmac · Post by **garthmac** » Thu Sep 16, 2004 4:29 am

No problem, glad to be of assistance!