Add 3 hours to Timestamp

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.

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.

William Thompson · Posted: Mon Jun 18, 2007 3:47 am

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....

Craq Giegerich · Posted: Mon Jun 18, 2007 5:43 am

dick scherrer · Posted: Mon Jun 18, 2007 6:00 am

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.

TG Murphy · Posted: Mon Jun 18, 2007 10:42 pm

How about:

SELECT CURRENT TIMESTAMP + 3 HOURS
FROM SYSIBM.SYSDUMMY1;

TG Murphy · Posted: Mon Jun 18, 2007 10:59 pm

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.

Craq Giegerich · Posted: Mon Jun 18, 2007 11:51 pm

TG Murphy · Posted: Tue Jun 19, 2007 12:16 am

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...

Craq Giegerich · Posted: Tue Jun 19, 2007 12:24 am

TG Murphy · Posted: Tue Jun 19, 2007 1:30 am

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...

TG Murphy · Posted: Tue Jun 19, 2007 1:52 am

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..

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.

William Thompson · Posted: Tue Jun 19, 2007 5:05 am

Jeya Raj · New User Joined: 14 Jan 2007 Posts: 33 Location: USA

Thanks TG. It worked fine.

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?

Craq Giegerich · Posted: Tue Jun 26, 2007 2:39 pm