Date Computation through DB2 Statements

sathyajes · New User Joined: 02 Mar 2006 Posts: 35 Location: Chennai

Hi,

I want to get the current date through a DB2 Statement.

And after get that I have to subract 'n' days(eg., 7 Days) from the current date and store the result on a variable.

Can it be possible thru DB2 Statements?
If yes, would it take care the leap year and stuff ie.,
for example if the current date is March'04-2006 then I have to get the computed date as 25-feb-2006.

Thanks,

martin9 · Posted: Tue Mar 14, 2006 9:36 pm

hy sathya,

yes this is possible.
SET :VAR = (CURRENT_DATE - 7)

you will get a correct date,
and you can be sure IBM implemented the date format correctly.

martin9

guptae · Posted: Wed Mar 15, 2006 3:33 pm

u can do it by
Select current_date - n days
into :var1
from SYSIBM.SYSDUMMY1.

Hope it will helpful

sathyajes · New User Joined: 02 Mar 2006 Posts: 35 Location: Chennai

Hi Martin & Gupta,

Thanks a lot for Responding to my Query.

When I tried your options , it gives me the following SQL Code

SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF - IS INVALID

My SQL was

SELECT (CURRENT_DATE - 7)
FROM SYSIBM.SYSDUMMY1

I tried it through QMF initially, though I wanted implemented this into a Program.

Could one of you help me ? - Am I going down on the Syntax?

Thanks,

guptae · Posted: Wed Mar 15, 2006 4:36 pm

Hi Sathya,

As i told u put
Select current_date - 7 days
from sysibm.sysdummy1

i tried & its wrking fine

thanooz · New User Joined: 28 Jun 2005 Posts: 99

hi satya,

can u trie this it will solve u r problem

SELECT DATE(DAYS(CURRENT_DATE)-7)FROM SYSIBM.SYSDUMMY1;

thak's
thanooz

sathyajes · New User Joined: 02 Mar 2006 Posts: 35 Location: Chennai

Hi Guys !

Its working ! thanks a lot for your replies - special sorry for missing the "Days".

Just for sharing, I am going to fix a production Bug permantely with this.
the abend was happening due a miscalculation of the leap year - this Job had ran on March'4th.

The purpose of the program is to extract the logs of different DB2 tables -between a week's range,it had abended giving out a B37 on the output file.

The B37 was b'cos, this a weekly job and start-date should have been Feb-25-2006 and logs should have been extracted from 25th Feb to March 4th.
Instead, the programatic logic had miscalculated it to be 01-Feb-2006.

Analysing this program, I thought can it not be handled with the DB2 statement itself , instead of the complex programatic logic ----> thus I posted this Query.

This program has been written in the yr 2000 and I really wonder why a complex logic was adapted to determine the start-date? --> Probably the then version of DB2 might not have supported "arithmetic cmputation" - Am I right?

Also can you guys recommend me any material which would have all the possible Arithmetic compution thru DB2 statements ?

Again, thanks a lot to all u guys ! see you soon with some other Query or answer to any one !