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 validation and date increment using SQL

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

New User


Joined: 15 Jun 2007
Posts: 92
Location: bangalore

PostPosted: Tue Mar 01, 2011 9:29 am    Post subject: date validation and date increment using SQL
Reply with quote

i want to increase the date using sql. i am trying the below query
SELECT DATE(:WS-RECV-DATE + 7 DAYS )
INTO :WS-RECV-DATE1
FROM SYSIBM.SYSDUMMY1

i have defined both host variables as pic x(10).
i am getting precompilation error.



And is there any way we can validate the date using SQL??

TIA
Back to top
View user's profile Send private message

rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Tue Mar 01, 2011 9:55 am    Post subject: Reply to: date validation and date increment using SQL
Reply with quote

Try the below:

Code:
SELECT DATE(:WS-RECV-DATE) + 7 DAYS
INTO :WS-RECV-DATE1
FROM SYSIBM.SYSDUMMY1


I tried it in SPUFI. Let me know it works
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 Mar 01, 2011 10:01 am    Post subject:
Reply with quote

Hello,

Did you use a "host variable" in SPUFI?
Back to top
View user's profile Send private message
chandracdac

New User


Joined: 15 Jun 2007
Posts: 92
Location: bangalore

PostPosted: Tue Mar 01, 2011 10:04 am    Post subject: Reply to: date validation and date increment using SQL
Reply with quote

Even the query what i have written is also working in QMF. but when i try the same in my cobol DB2 program it says UNDEFINED OR UNUSABLE HOST VARIABLE
Back to top
View user's profile Send private message
chandracdac

New User


Joined: 15 Jun 2007
Posts: 92
Location: bangalore

PostPosted: Tue Mar 01, 2011 10:20 am    Post subject: Reply to: date validation and date increment using SQL
Reply with quote

we cant use host variables in spufi or qmf ...
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 Mar 01, 2011 10:35 am    Post subject: Reply to: date validation and date increment using SQL
Reply with quote

Yes, that is why i asked the question. . .

d
Back to top
View user's profile Send private message
chandracdac

New User


Joined: 15 Jun 2007
Posts: 92
Location: bangalore

PostPosted: Tue Mar 01, 2011 10:38 am    Post subject: Reply to: date validation and date increment using SQL
Reply with quote

so is there any other way that i can validate the date using SQL ??
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 Mar 01, 2011 10:46 am    Post subject:
Reply with quote

Hello,

Where i am this evening i'm not connected to a mainframe so i can't post something specific i've tested..

See if this prior topic helps:
http://ibmmainframes.com/viewtopic.php?t=26933
Back to top
View user's profile Send private message
chandracdac

New User


Joined: 15 Jun 2007
Posts: 92
Location: bangalore

PostPosted: Tue Mar 01, 2011 10:58 am    Post subject:
Reply with quote

It is like i have to take the date from input file calculate the date +7 days and then put it again in another file...

i just want to know how can we use SQL for date increment.
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Tue Mar 01, 2011 11:01 am    Post subject:
Reply with quote

Hello Dick Scherrer,

Of course I can't use a host variable in SPUFI and neither can we use "INTO". I put in a hard-coded value in place of host-variable and removed the INTO line. I think I need to be more careful with my assumptions.

I tried as below for a valid date
Code:
SELECT DATE('2010-03-01') + 7 DAYS
 FROM SYSIBM.SYSDUMMY1   


Output:
Code:
2010-03-08


For invalid date,

Code:
SELECT DATE('20100301  ') + 7 DAYS     
 FROM SYSIBM.SYSDUMMY1                 


Code:
DSNE610I NUMBER OF ROWS DISPLAYED IS 0                                         
DSNT408I SQLCODE = -180, ERROR:  THE DATE, TIME, OR TIMESTAMP VALUE *N IS       
         INVALID                                                               
DSNT418I SQLSTATE   = 22007 SQLSTATE RETURN CODE                               


Hello chandracdac,

You can validate the date using db2. If in the above query, you pass in an invalid date format, you should get sqlcode=-180 as above.

Else you should get the sqlcode= 000 with the incremented date in the WS-RECV-DATE1 variable.

-Thanks,
Rakesh.
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 Mar 01, 2011 11:05 am    Post subject:
Reply with quote

Hello,

Quote:
i just want to know how can we use SQL for date increment.
Did you even look at the linked previous topic?

If not, do so now. . .

If you did, why will something like this not work. . . icon_confused.gif
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 Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
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


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