Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Date Computation through DB2 Statements

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sathyajes

New User


Joined: 02 Mar 2006
Posts: 24
Location: Chennai

PostPosted: Tue Mar 14, 2006 9:09 pm    Post subject: Date Computation through DB2 Statements
Reply with quote

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,
Back to top
View user's profile Send private message

martin9

Active User


Joined: 01 Mar 2006
Posts: 287
Location: Basel, Switzerland

PostPosted: Tue Mar 14, 2006 9:36 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Mar 15, 2006 3:33 pm    Post subject:
Reply with quote

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

Hope it will helpful
Back to top
View user's profile Send private message
sathyajes

New User


Joined: 02 Mar 2006
Posts: 24
Location: Chennai

PostPosted: Wed Mar 15, 2006 3:54 pm    Post subject: Re: Date Computation through DB2 Statements
Reply with quote

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,
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Mar 15, 2006 4:36 pm    Post subject:
Reply with quote

Hi Sathya,

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

i tried & its wrking fine
Back to top
View user's profile Send private message
thanooz

New User


Joined: 28 Jun 2005
Posts: 99

PostPosted: Wed Mar 15, 2006 4:37 pm    Post subject:
Reply with quote

hi satya,


can u trie this it will solve u r problem

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



thak's
thanooz
Back to top
View user's profile Send private message
sathyajes

New User


Joined: 02 Mar 2006
Posts: 24
Location: Chennai

PostPosted: Wed Mar 15, 2006 5:27 pm    Post subject: Re: Date Computation through DB2 Statements
Reply with quote

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 !
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts Overlay the system date Danielle.Filteau SYNCSORT 4 Fri Mar 03, 2017 1:16 am
No new posts Checking Current date vickey_dw DFSORT/ICETOOL 1 Wed Feb 08, 2017 11:14 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us