View previous topic :: View next topic
|
Author |
Message |
Jeya Raj
New User
Joined: 14 Jan 2007 Posts: 33 Location: USA
|
|
|
|
Hi,
I'm getting process timesatmp(WS-TIMESTAMP) calling task(subprogram) and i need to 3 hours into it. My WS-TIMESTAMP is X(26)(format is CCYY-MM-DD-HH-MM-SEC.NSEC).
I used
EXEC SQL
SET WS-TIMESTAMP = (WS-TIMESTAMP + 3 hours)
END-EXEC.
It is giving compilation error.
Is there any other way to add 3 hours into timestamp. I have to use this modified Timesatmp into cursor as host variable.
Thanks. |
|
Back to top |
|
|
Jeya Raj
New User
Joined: 14 Jan 2007 Posts: 33 Location: USA
|
|
|
|
Hi,
I'm getting process timesatmp(WS-TIMESTAMP)by calling task(subprogram) and i need to add 3 hours into it. My WS-TIMESTAMP is X(26)(format is CCYY-MM-DD-HH-MM-SEC.NSEC).
I used
EXEC SQL
SET WS-TIMESTAMP = (WS-TIMESTAMP + 3 hours)
END-EXEC.
It is giving me compilation error.
Is there any other way to add 3 hours into timestamp. I have to use this modified Timesatmp into cursor as a host variable.
Thanks. |
|
Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
|
|
Slowdown, double posting will get you into trouble....Haven't you read the forum rules and guidlines?
It is Sunday (still), give it a break.....
You have (almost) been warned.... |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Jeya Raj wrote: |
Hi,
I'm getting process timesatmp(WS-TIMESTAMP)by calling task(subprogram) and i need to add 3 hours into it. My WS-TIMESTAMP is X(26)(format is CCYY-MM-DD-HH-MM-SEC.NSEC).
I used
EXEC SQL
SET WS-TIMESTAMP = (WS-TIMESTAMP + 3 hours)
END-EXEC.
It is giving me compilation error.
Is there any other way to add 3 hours into timestamp. I have to use this modified Timesatmp into cursor as a host variable.
Thanks. |
Code: |
EXEC SQL
SET :WS-TIMESTAMP = TIMESTAMP(:WS-TIMESTAMP) + 3 hours
END-EXEC.
|
You were close. But please post your error messages in the future so people don't have to ask for them! |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Also, please keep in mind that questions posted on Sat and Sun are likely to see slower response time than those posted during the week.
As Bill cautions, double posting is not proper. Posting the same question in multiple forums is also a no-no.
When you don't receive a reply as quickly as you'd prefer, hang in there. For most questions, someone will have a suggestion/solution - it just may take a little while. |
|
Back to top |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007 Posts: 148 Location: Ottawa Canada
|
|
|
|
How about:
SELECT CURRENT TIMESTAMP + 3 HOURS
FROM SYSIBM.SYSDUMMY1; |
|
Back to top |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007 Posts: 148 Location: Ottawa Canada
|
|
|
|
Sorry - the solution I provided adds 3 hours to the current time. I see you want to add 3 hours to your own timestamp value:
See if the following works...
MOVE "2007-06-11-23.59.59.123456" TO WS-TIMESTAMP
SELECT :WS-TIMESTAMP + 3 HOURS
FROM SYSIBM.SYSDUMMY1
I also have COBOL logic that converts a DB2 timestamp into absolute microseconds (1 million microseconds in a second). And another section that converts it back from microseconds to DB2 format.
There are 180,000,000 microseconds in 3 hours (3 * 60 * 1000000). So the logic would be:
1. Convert timestamp to microseconds.
2. Add 180,000,000 to microseconds.
3. Convert microseconds back to timestamp.
Of course this logic is best packaged as a set of copybooks or as a utility. The DB2 approach is so simple so you would only need to use the COBOL approach if you had a good reason for avoiding DB2.
If you need the conversion logic - just ask and I can post. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
TG Murphy wrote: |
Sorry - the solution I provided adds 3 hours to the current time. I see you want to add 3 hours to your own timestamp value:
See if the following works...
MOVE "2007-06-11-23.59.59.123456" TO WS-TIMESTAMP
SELECT :WS-TIMESTAMP + 3 HOURS
FROM SYSIBM.SYSDUMMY1
I also have COBOL logic that converts a DB2 timestamp into absolute microseconds (1 million microseconds in a second). And another section that converts it back from microseconds to DB2 format.
There are 180,000,000 microseconds in 3 hours (3 * 60 * 1000000). So the logic would be:
1. Convert timestamp to microseconds.
2. Add 180,000,000 to microseconds.
3. Convert microseconds back to timestamp.
Of course this logic is best packaged as a set of copybooks or as a utility. The DB2 approach is so simple so you would only need to use the COBOL approach if you had a good reason for avoiding DB2.
If you need the conversion logic - just ask and I can post. |
And just where is DB2 going to put ws-timestamp after is adds 3 hours to it. |
|
Back to top |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007 Posts: 148 Location: Ottawa Canada
|
|
|
|
Craq,
Does this look better?
EXEC SQL SELECT
TIMESTAMP(:WS-TIMESTAMP) + 3 HOURS
INTO :WS-NEW-TIMESTAMP
FROM SYSIBM.SYSDUMMY1
END-EXEC
I haven't tested so you may need to tinker with what I gave you.
The first SQL I proposed above was in SPUFI format so that is how I managed to leave out the INTO... |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
TG Murphy wrote: |
Craq,
Does this look better?
EXEC SQL SELECT
TIMESTAMP(:WS-TIMESTAMP) + 3 HOURS
INTO :WS-NEW-TIMESTAMP
FROM SYSIBM.SYSDUMMY1
END-EXEC
I haven't tested so you may need to tinker with what I gave you.
The first SQL I proposed above was in SPUFI format so that is how I managed to leave out the INTO... |
Why bother with reference to a table when the SET does the samething with out any table reference. |
|
Back to top |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007 Posts: 148 Location: Ottawa Canada
|
|
|
|
Sure. Agree.
I was under the impression that the syntax "+ 3 MONTHS" for some reason did not work with SET.
I sure could be wrong about that - if you can get it working using the SET command then I agree it is a better way than having to reference SYSIBM.SYSDUMMY1.
I don't have the time to test it all out at the moment. Maybe somebody can do this... |
|
Back to top |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007 Posts: 148 Location: Ottawa Canada
|
|
|
|
Craq,
You were right. No need to use SELECT from SYSIBM.SYSDUMMY1.
I tested it and the following works fine...
MOVE "1999-01-01-14.59.59.123456" TO WS-TS
EXEC SQL SET :WS-NEW-TS = TIMESTAMP(:WS-TS) + 3 HOURS
END-EXEC
The only thing I can think of is that original SQL (see first post above) did not specify the colon to start off the host variable.
Good catch Craq.. |
|
Back to top |
|
|
Jeya Raj
New User
Joined: 14 Jan 2007 Posts: 33 Location: USA
|
|
|
|
Thanks all for your tips. I'll try it tonight.
Thanks for your warning William. I forgot about the rules. |
|
Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
|
|
Jeya Raj wrote: |
Thanks all for your tips. I'll try it tonight. |
Good luck, and remember, we are here..... |
|
Back to top |
|
|
Jeya Raj
New User
Joined: 14 Jan 2007 Posts: 33 Location: USA
|
|
|
|
Thanks TG. It worked fine. |
|
Back to top |
|
|
rsundar83
New User
Joined: 05 Dec 2005 Posts: 1
|
|
|
|
Hi,
With the above command, the module did not give any compilation error but the job abended exactly when it was processing this SQL.
WS-UPDT-TMSTMP = 2005-02-17-21.44.47.292406
EXEC SQL
SET :WS-CST-TMSTMP = TIMESTAMP(:WS-UPDT-TMSTMP) + 6 HOURS
END-EXEC.
fyi, WS-CST-TMSTMP & WS-UPDT-TMSTMP are declared as X(26).
any guess why?? i suspected precompiler options. But it looks fine to me. Can anyone please help me on this? |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
rsundar83 wrote: |
Hi,
With the above command, the module did not give any compilation error but the job abended exactly when it was processing this SQL.
WS-UPDT-TMSTMP = 2005-02-17-21.44.47.292406
EXEC SQL
SET :WS-CST-TMSTMP = TIMESTAMP(:WS-UPDT-TMSTMP) + 6 HOURS
END-EXEC.
fyi, WS-CST-TMSTMP & WS-UPDT-TMSTMP are declared as X(26).
any guess why?? i suspected precompiler options. But it looks fine to me. Can anyone please help me on this? |
Would you be willing to share the abend messages etc with us? What was the value of ws-updt-tmstmp when the abend occured? |
|
Back to top |
|
|
|