overriding current date in cursor

Grant Mackay

overriding current date in cursor
I have a cobol program that opens a cursor which does some date calculations...

select days(current date) - days(evt_dt)
from tablea
where days(current date) - days(evt_dt) > 0

For QA testing I want to be able to override the current date in the sql, so I
don't have to wait a month for the test condition to be met.

I know I can run the following sql statement.

select days('2001-01-31') - days(evt_dt)
from tablea
where days('2001-01-31') - days(evt_dt) > 0

Other than dynamic sql in the cobol program, how do I get a host variable to
replace the current date?

This didn't work to well.
select days(:WS-CURRENT-DATE) - days(evt_dt)
from tablea
where days(:WS-CURRENT-DATE) - days(evt_dt) > 0



Terry Purcell

Re: overriding current date in cursor
(in response to Grant Mackay)
Grant,

Try
select days(DATE(:WS-CURRENT-DATE)) - days(evt_dt)
from tablea
where days(DATE(:WS-CURRENT-DATE)) - days(evt_dt) > 0

DB2 can cast the datatype of literal values, ie. there is no fixed datatype,
so it assumes one. When you specify a host variable, DB2 picks up the
datatype from your explicit definition. In this case, probably PIC X(10) or
CHAR(10) in DB2 speak. Date arithmetic can only be done on date datatypes.

Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Grant Mackay
Sent: Friday, January 12, 2001 1:18 PM
To: [login to unmask email]
Subject: overriding current date in cursor


I have a cobol program that opens a cursor which does some date
calculations...

select days(current date) - days(evt_dt)
from tablea
where days(current date) - days(evt_dt) > 0

For QA testing I want to be able to override the current date in the sql, so
I
don't have to wait a month for the test condition to be met.

I know I can run the following sql statement.

select days('2001-01-31') - days(evt_dt)
from tablea
where days('2001-01-31') - days(evt_dt) > 0

Other than dynamic sql in the cobol program, how do I get a host variable to
replace the current date?

This didn't work to well.
select days(:WS-CURRENT-DATE) - days(evt_dt)
from tablea
where days(:WS-CURRENT-DATE) - days(evt_dt) > 0