Converting String field to Time field
Moderators: chulett, rschirm, roy
Converting String field to Time field
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 ?
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 ?
-
- Participant
- Posts: 40
- Joined: Tue Mar 02, 2004 8:21 am
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.
In Unix type:
Code: Select all
db2 describe TABLE_NAME
i am assuming it will have the following format:
Code: Select all
TIME 3
Code: Select all
oconv(Variable_of_Time, "MTS")
-
- Participant
- Posts: 40
- Joined: Tue Mar 02, 2004 8:21 am
try:
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
Code: Select all
oconv(Iconv(YOUR_VARIABLE, "MTS"), "MTS")
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
-
- Participant
- Posts: 40
- Joined: Tue Mar 02, 2004 8:21 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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
This gave the following results:
2 gave 2 (no zeros or colons)
258 gave 178.
When I tested the above, it gave no output at all for any figures I put in.
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.
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")
2 gave 2 (no zeros or colons)
258 gave 178.
Code: Select all
Ans = Iconv(Fmt(Arg1, "6'0'R##:##:##"), "MT")
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")
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 casegives a human-readable time.
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%%:%%:%%")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.