|
View previous topic :: View next topic
|
| Author |
Message |
chandracdac
New User
Joined: 15 Jun 2007
Posts: 92
Location: bangalore
|
|
|
|
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 |
|
 |
rakesh1155
New User
Joined: 21 Jan 2009
Posts: 84
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Did you use a "host variable" in SPUFI? |
|
| Back to top |
|
|
chandracdac
New User
Joined: 15 Jun 2007
Posts: 92
Location: bangalore
|
|
|
|
| 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 |
|
|
chandracdac
New User
Joined: 15 Jun 2007
Posts: 92
Location: bangalore
|
|
|
|
| we cant use host variables in spufi or qmf ... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Yes, that is why i asked the question. . .
d |
|
| Back to top |
|
|
chandracdac
New User
Joined: 15 Jun 2007
Posts: 92
Location: bangalore
|
|
|
|
| so is there any other way that i can validate the date using SQL ?? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
| Back to top |
|
|
chandracdac
New User
Joined: 15 Jun 2007
Posts: 92
Location: bangalore
|
|
|
|
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 |
|
|
rakesh1155
New User
Joined: 21 Jan 2009
Posts: 84
Location: India
|
|
|
|
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:
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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. . .  |
|
| Back to top |
|
|
|
|
