Getting predated or post dated date from Sysdummy1.



IBM's flagship relational database management system

Getting predated or post dated date from Sysdummy1.

Postby gokulNmf » Mon Jul 23, 2012 7:30 pm

Hi,
This is how we used to get date from SYSIBM.SYSDUMMY1 table,
SELECT DATE(CURRENT DATE + 1 DAYS),
 INTO :WS-MON-DATE-DB2,
 FROM SYSIBM.SYSDUMMY1
 WITH UR;


Now my requirement is, if suppose on the particular day i am not able to run the program as scheduled, i want to run program on later date by giving the past date through a parm to the program. But how to pass that value to this table to get the date? I tried the below code, but its giving error while binding. (-182, -171). Is there any way to achieve this?

SELECT DATE(:WS-CURRENT-DATE-DB2-H + 1 DAYS),
INTO :WS-MON-DATE-DB2,
FROM SYSIBM.SYSDUMMY1
WITH UR
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 118
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 2 times
Been thanked: 0 time

Re: Getting predated or post dated date from Sysdummy1.

Postby Akatsukami » Mon Jul 23, 2012 8:06 pm

And :WS-CURRENT-DATE-DB2-H is defined as...?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Getting predated or post dated date from Sysdummy1.

Postby gokulNmf » Mon Jul 23, 2012 10:55 pm

I am sorry, i dint mention it.

WS-CURRENT-DATE-DB2-H pic x(10) and i am moving the date in 'yyyy-mm-dd' format to it.
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 118
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 2 times
Been thanked: 0 time

Re: Getting predated or post dated date from Sysdummy1.

Postby Akatsukami » Tue Jul 24, 2012 12:35 am

I believe that the problem is that COBOL doesn't have any equivalent data type to a DB2 DATE INTERNAL; it is thus necessary to cast the character string to a date before attempting to perform date arithmetic on it. Try:

SELECT DATE(:WS-CURRENT-DATE-DB2-H)+ 1 DAYS
INTO :WS-MON-DATE-DB2
FROM SYSIBM.SYSDUMMY1
WITH UR
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day

These users thanked the author Akatsukami for the post:
chandurokzz (Fri Aug 10, 2012 10:49 am)
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Getting predated or post dated date from Sysdummy1.

Postby gokulNmf » Tue Jul 24, 2012 8:53 am

And what you believed is true Akatsukami, its the problem with datatype, i tried the below one as you suggested, by just tweaking, and it just worked fine :D .
EXEC SQL                                             
    SELECT DATE(DATE(:WS-CURRENT-DATE-DB2-H)+ 1 DAYS)
      INTO :WS-MON-DATE-DB2                         
      FROM SYSIBM.SYSDUMMY1                         
END-EXEC                                             


Thanks for the reply!
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 118
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 2 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post