Date + 1 days

kedianirmal · Posted: Mon Aug 31, 2009 9:10 pm

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

Craq Giegerich · Posted: Mon Aug 31, 2009 9:15 pm

It would help if you show us the actual code you are using.

GuyC · Posted: Mon Aug 31, 2009 9:22 pm

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

kedianirmal · Posted: Mon Aug 31, 2009 9:23 pm

The table has date in the format:
DD.MM.YYYY

kedianirmal · Posted: Mon Aug 31, 2009 9:25 pm

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.

GuyC · Posted: Mon Aug 31, 2009 9:31 pm

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.

GuyC · Posted: Mon Aug 31, 2009 9:33 pm

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

kedianirmal · Posted: Mon Aug 31, 2009 9:45 pm

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.

lkhiger · New User Joined: 28 Oct 2005 Posts: 89

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