View previous topic :: View next topic
|
Author |
Message |
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006 Posts: 216 Location: USA
|
|
|
|
Hi,
My file(RECFM=VB) is created by a CICS program which moves EIBDATE(S9(7) COMP-3 - 0CYYDDD+) and EIBTIME(S9(7) COMP-3 - 0HHMMSS+) values into a field. I used the below sort to convert them into the desired format. I am convinced about the date conversion, but can someone please explain if the time shown is correct? The actual requirement is to create a DB2 timestamp out of those 2 fields.
Code: |
OUTREC BUILD=(1,4,18,3,Y2U,TOGREG=Y4T(-),C'-',21,4,PD,TO=ZD,LENGTH=7) |
The output is
Code: |
2012-10-17-0021120
2012-10-17-0021120
2012-10-17-0029560
2012-10-17-0029560
2012-10-17-0051360
2012-10-17-0051360
2012-10-17-0056060
2012-10-17-0056060
2012-10-17-0100490
2012-10-17-0100490 |
By the hex values of those 10 records are as below.
Code: |
21210210
029F012F
21210210
029F012F
21210250
029F096F
21210250
029F096F
21210530
029F016F
21210530
029F016F
21210500
029F066F
21210500
029F066F
21210040
029F109F
21210040
029F109F |
Based on my analysis it seems the results produced by DFSORT are correct? Is the data incorrect in the file or are there any problems in the timeconversion? Can someone please suggest any better way if exists to convert the DATE,TIME fields into DB2 timestamp format? YYYY-MM-DD-HH.MM.SS.000000 |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
YYYY-MM-DD-HH.MM.SS.000000 |
if you want the above to be your 'timestamp' format, great.
depends on the ultimate purpose of your generated 'timestamp'.
most of the time yyyy-mm-dd-hh:mm:ss:mmmmmm is the pattern
but that can be overridden with 'timestamp format' parm in the timestamp scalar function in db2 10
(probably earlier, but i am to lazy to look
since I have always used yyyy-mm-dd-hh:mm:ss:mmmmmm
as my pattern and never have had any trouble using that as a host variable).
i am ignoring the 'hex values' that you have provided.
they make little or no sense.
so, why don't you tell us for what use you intend your 'generated timestamp'
and we may be able to help.
2012-10-17-0021120 is 2012-10-17-0hhmmss
so what you are generating now is useless. |
|
Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006 Posts: 216 Location: USA
|
|
|
|
Hi dbzTHEdinosauer,
I thought the same way as you did about the time format that 0021120 is 0hhmmss. But it was actually hhmmss0.
So the times and hex values are clear. The time here is 00:21:12 in the way you want to see.
I got the problem resolved. Thanks for the information provided.
Regards
Amar |
|
Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
V S Amarendra Reddy,
You need to use PD0 format which ignores the first digit and the sign. Use the following control cards to create a 26 byte timestamp field.
Code: |
OUTREC BUILD=(1,4,18,3,Y2U,TOGREG=Y4T(-),C'-',
21,4,PD0,EDIT=(TT:TT:TT),C'.000000')
|
|
|
Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006 Posts: 216 Location: USA
|
|
|
|
Thank you Skolusu |
|
Back to top |
|
|
|