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 + 1 days

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

New User


Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India

PostPosted: Mon Aug 31, 2009 9:10 pm    Post subject: Date + 1 days
Reply with quote

Hi,

My requirement is to read min(end_date) + 1 days in a cursor.
While fetching the data from the cursor the program is failing with -183.
Can somebody help if this is possible in cursor or what is the correct syntax.

Thanks,
Nirmal
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Aug 31, 2009 9:15 pm    Post subject:
Reply with quote

It would help if you show us the actual code you are using.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Aug 31, 2009 9:22 pm    Post subject:
Reply with quote

probably your min(end_date) = 9999-12-31
If you add 1 day you'll get -183

Possible solution is
case Min(end_date) < '9999-12-31'
then min(end_date) + 1 days
else '9999-12-31'
end
Back to top
View user's profile Send private message
kedianirmal

New User


Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India

PostPosted: Mon Aug 31, 2009 9:23 pm    Post subject:
Reply with quote

The table has date in the format:
DD.MM.YYYY
Back to top
View user's profile Send private message
kedianirmal

New User


Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India

PostPosted: Mon Aug 31, 2009 9:25 pm    Post subject:
Reply with quote

The cursor has select as below:
SELECT JS_PROD_CODE
,BR_CODE
,MIN(END_DATE + 1 DAYS )
FROM LNCVA098

I can fetch the minimum date and can add +1 day using intrinsic funtion but i am looking if cursor can directly give the desired output.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Aug 31, 2009 9:31 pm    Post subject:
Reply with quote

1) At least code min(end_date) + 1 days
it gives the same result as min(end_date + 1 days) but could be resolved using an index.

2) you'll need a group by

3) a table does not have dates in format DD.MM.YYYY
It can be that your db2 has external date representation dd.mm.yyyy.
just change 9999-12-31 to 31.12.9999 in my solution.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Aug 31, 2009 9:33 pm    Post subject:
Reply with quote

ps.
min(end_date + 1 days) will fail if any end_date = 31.12.9999
min(end_date) + 1 days will only fail when the min(end_date) = 31.12.9999
Back to top
View user's profile Send private message
kedianirmal

New User


Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India

PostPosted: Mon Aug 31, 2009 9:45 pm    Post subject:
Reply with quote

Thanks a lot,

The issue was of date 31.12.9999 only.
I added a where as AND END_DATE < '31.12.9999'
its working fine. Will test actual results tomorrow.
Thanks frineds.
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sun Sep 06, 2009 11:15 pm    Post subject:
Reply with quote

That's right. If the date = MAX date allowed in DB2 you can accept this SQLCODE.
Also you have not use "DAYS" but "DAY":

Code:
min(end_date) + 1 day


Because "DAYS" is the scalar function to find the difference between dates.

Lenny
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 TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am


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