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
Fetch seven days old data
Moderators: chulett, rschirm, roy
Assuming effdt is an actual date and BIzDate is a string, don't you need to do something more like:
![Confused :?](./images/smilies/icon_confused.gif)
Code: Select all
Select * from Temptable where effdate > date('#BIzDate#') - 7 days and effdate <= date('#BIzDate#')
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India