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: 1280
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: 1280
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: 1280
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 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