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: 87
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: 87
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: 87
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: 87
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: 87
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 TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am


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