Fetch seven days old data

[srai]

Hi Team,

We have a requirement to develop a datastage job which fetch 7 days old data in every run. Our source in DB2.

I have tried below user defined query in DB2

Select * from Temptable where effdate >'#BIzDate#-7' and effdate <='#BIzDate#'

But it did not work. BIzDate is a parameter which we are passing as 20110324 for todays run and final query should be formed as

Select * from Temptable where effdate >'20110317' and effdate <='20110324'
We need to implement all this in DB2 source stage only.

Any help on this would be great.


Thanks
Santosh

[chulett]

Assuming effdt is an actual date and BIzDate is a string, don't you need to do something more like:

Code: Select all

Select * from Temptable where effdate > date('#BIzDate#') - 7 days and effdate <= date('#BIzDate#')

[srai]

Thanks Chulett.

Yes effdt is an actual date but its also coming in format 20110131.
I tried the solution mentioned by you but still not able to fetch data.

Please help me if I am doing something worng. It is just a select query.

Thanks,
Santosh

[ray.wurlod]

If BizDate is an actual date it is NOT coming in that format - it's a binary value. Create a new parameter, say jpBizDateLastWeek and do the arithmetic when providing that parameter value.

[chulett]

Get it working outside of DataStage first. Surely there are DB2 savvy people there who can help you? Once you have it working, you can easily get it back into your job.

[vinothkumar]

SELECT DATE('2001-09-22'), DATE('2001-09-22') - 7 DAYS FROM SYSIBM.SYSDUMMY1;

Use this as base and try to modify as per your requirement.