Page 1 of 2
Converting String field to Time field
Posted: Wed Sep 15, 2004 4:27 am
by JezT
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 ?
Posted: Wed Sep 15, 2004 5:22 am
by Steve@HSBC
What is the format of the DB2 table column you are trying to load?
In Unix type:
and look at the format of the field you are trying to load.
i am assuming it will have the following format:
If so i would do this to your time:
Think that should help / point you in the right direction.
Posted: Wed Sep 15, 2004 6:59 am
by JezT
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).
Posted: Wed Sep 15, 2004 7:23 am
by Steve@HSBC
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
Posted: Wed Sep 15, 2004 7:30 am
by JezT
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 !!
Posted: Wed Sep 15, 2004 8:08 am
by Steve@HSBC
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
Posted: Wed Sep 15, 2004 8:09 am
by garthmac
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")
Posted: Wed Sep 15, 2004 8:26 am
by garthmac
Sorry, this is cleaner:
Zeros = FMT(Arg1,"6'0'R")
Ans = Iconv(Zeros[1,2]:":":Zeros[3,2]:":":Zeros[5,2],"MTS")
Posted: Wed Sep 15, 2004 4:26 pm
by ray.wurlod
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")
Posted: Thu Sep 16, 2004 2:11 am
by JezT
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.
Posted: Thu Sep 16, 2004 2:25 am
by ray.wurlod
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
gives a human-readable time.
Posted: Thu Sep 16, 2004 2:49 am
by garthmac
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.
Posted: Thu Sep 16, 2004 3:44 am
by JezT
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
Posted: Thu Sep 16, 2004 3:44 am
by JezT
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
Posted: Thu Sep 16, 2004 4:29 am
by garthmac
No problem, glad to be of assistance!
