View previous topic :: View next topic
|
Author |
Message |
samedesh Currently Banned New User
Joined: 22 May 2007 Posts: 46 Location: India
|
|
|
|
Hello All,
I am writing a cobol pgm and have the requirement to perform the addition on time variable. Is their any DB2 inbuilt utility which can perform computations on time.
I want 09:00 + 00:15 = 09:15, something like this.
Thanks |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
If your module is already executing SQL,
then simply adding some sql to perform date time arithmetic (see sql manuals - IBM manuals button at top of page)
is easy.
if your module does not have a db2 requirement other than your date time arithmetic,
then look at the LE manuals
this means that you will have to make several calls - convert date/time
- subtract converted values
- convert the difference from the subtraction to a date time
now this sounds like a lot of trouble,
but a programmers time is nowhere as expensive as db2 resources.
and if your requirement is only a simple addition/subtraction of time
without worrying about date
is very simple,
probably already on the web someplace. |
|
Back to top |
|
|
samedesh Currently Banned New User
Joined: 22 May 2007 Posts: 46 Location: India
|
|
|
|
Hi,
I tried following query for substraction and it worked fine
SELECT TIME(Column A) - TIME(Column B)
FROM Table
But when I tried for addition it is giving me -171 sqlcode.
SELECT TIME(Column A) + TIME(Column B)
FROM Table
Please guide.
Thanks |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
as you can see from the FM you can find the difference between data/time/timestamps but you can only increment or decrement them.
and the results of the subtraction is the interval in time units. not an actual time.
best way is - RTFM
- add or subtract a time interval (again refer to the manual) from a date/time datatype to return a modified date/time datatype
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
you can also try these sql statements and learn about date/time arithmetic
Code: |
SELECT CURRENT_TIME
FROM SYSIBM.SYSDUMMY1
;
SELECT CURRENT_TIME + 1 HOUR
, CURRENT_TIME
FROM SYSIBM.SYSDUMMY1
;
SELECT ((CURRENT_TIME + 1 HOUR) + 1 MINUTES) + 1 SECONDS
, CURRENT_TIME
FROM SYSIBM.SYSDUMMY1
;
SELECT (((CURRENT_TIME + 1 HOUR) + 1 MINUTES) + 1 SECONDS) + 1 DAY
, CURRENT_TIME
FROM SYSIBM.SYSDUMMY1
; |
|
|
Back to top |
|
|
samedesh Currently Banned New User
Joined: 22 May 2007 Posts: 46 Location: India
|
|
|
|
I cannot hardcode the values as both are coming from columns of a table.
No idea what values may come. I have to put this in the cobol code which is having other DB2 queries also. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
RTFM about date-time functions
you've got hour(), minute(), second() ,midnight_seconds() ,... to work with.
f.e. : colA + hour(colB) hours |
|
Back to top |
|
|
|