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
 

 

ADD_MONTHS in DB2

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

New User


Joined: 14 Mar 2008
Posts: 74
Location: India,Chennai

PostPosted: Tue Aug 21, 2012 9:29 pm    Post subject: ADD_MONTHS in DB2
Reply with quote

Hi,
I am using 'ADD_MONTHS' table to calculate date in a cobol program.

EXEC SQL
SELECT ADD_MONTHS(:WS-date1,:WS-year)
INTO :WS-date2
FROM SYSIBM.SYSDUMMY1
END-EXEC

I am getting a compilation error 'UNDEFINED OR UNUSABLE HOST VARIABLE "WS-YEAR" .
Can any one let me know if the year part must be a numeric expression only. Something like:
EXEC SQL
SELECT ADD_MONTHS(:WS-date1,+4) INTO :WS-date2
FROM SYSIBM.SYSDUMMY1
END-EXEC

Thanks in advance,
Sujesh.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Aug 21, 2012 10:22 pm    Post subject:
Reply with quote

Hello,

How are the columns in the table and the host variables defined?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Tue Aug 21, 2012 10:23 pm    Post subject:
Reply with quote

I am not sure what ADD_MONTHS table is but DB2 is very good at date arithmetic.

Code:
EXEC SQL
SET :WS-DATE2 = DATE(:WS-DATE1) + :WS-NUM-MONTHS MONTHS
END-EXEC.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Aug 22, 2012 12:14 pm    Post subject:
Reply with quote

if you want to use a host-variable as a date,it is always smart to use date(:hv)
Back to top
View user's profile Send private message
Prakash J

New User


Joined: 23 May 2012
Posts: 14
Location: India

PostPosted: Wed Aug 22, 2012 2:21 pm    Post subject: Reply to: ADD_MONTHS in DB2
Reply with quote

Hello,

You can use.

The host variable you are using to add no. of months (in your code, :WS-year) should be defined S9(2) COMP OR S9(2) COMP-3. May be, you defined it as 9(2).

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



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