getting rows between two dates
Moderators: chulett, rschirm, roy
getting rows between two dates
Hi,
I have requirment that we need to get row between two days. Eg:
Start_date and EndDate
Mar-6- 2009 to Mar-12-2009
I need to get the row between two dates in another colum
so it should be
Mar-6-2009
Mar-7-2009
Mar-8-2009
Mar-9-2009
Mar-10-2009
Mar-11-2009
Mar-12-2009
And again start the new startdate and NewEndate . is it possible in datastage ? please let me know. Thank you.
I have requirment that we need to get row between two days. Eg:
Start_date and EndDate
Mar-6- 2009 to Mar-12-2009
I need to get the row between two dates in another colum
so it should be
Mar-6-2009
Mar-7-2009
Mar-8-2009
Mar-9-2009
Mar-10-2009
Mar-11-2009
Mar-12-2009
And again start the new startdate and NewEndate . is it possible in datastage ? please let me know. Thank you.
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
Re: getting rows between two dates
CAN YOU BE MORE SPECIFIC??
Re: getting rows between two dates
Hi,
From what i understood, you need to generate date values(rows) between 2 given dates.
It is possible in DataStage.
You can write a server routine to do this.
even an unix script can be called from DS to achive this.
But it depends on what is your source, target and how you want to
use these values.
If you provide more details, we might help further.
From what i understood, you need to generate date values(rows) between 2 given dates.
It is possible in DataStage.
You can write a server routine to do this.
even an unix script can be called from DS to achive this.
But it depends on what is your source, target and how you want to
use these values.
If you provide more details, we might help further.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you want to apply the query in the source itself, use the condition below -
If you want to achieve this in DataStage, use the constraint below in your Transformer -
However, although you have posted in the Server forum, you have mentioned Parallel in your post, which would render the second solution useless, unless you use a Basic Transformer.
Code: Select all
to_char(your_date_column, 'YYYYMMDD') BETWEEN '20090306' AND '20090312'
Code: Select all
Oconv(Iconv(your_date_column, "D4-MDY[A3]"), "DYMD[4,2,2]") > '20090306' AND Oconv(Iconv(your_date_column, "D4-MDY[A3]"), "DYMD[4,2,2]") < '20090312'
gateleys
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
OK, you're getting the two dates from Oracle and you want to generate one row for each date in the range? Create one job to read from Oracle and the other to generate the rows, which receives the two dates as parameters.
In the first job invoke UtilityRunJob() to run the second job and pass it the two dates.
In the first job invoke UtilityRunJob() to run the second job and pass it the two dates.
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.
Wait... you can use the between like that after the to_char() to cause it to generate multiple values? In all my born days I've never seen the like and me with no way to test it right now.gateleys wrote:If you want to apply the query in the source itself, use the condition below -Code: Select all
to_char(your_date_column, 'YYYYMMDD') BETWEEN '20090306' AND '20090312'
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Hey Craig, Thanks for the sarcasm. However, please tell me what is wrong with the above to extract dates between 2 specified dates. In fact, I am in front of my computer and just tested the query and it works fine. Now, its different if you were suggesting not being able to do "something else" with this query, in which case, it was never intended to.chulett wrote:Wait... you can use the between like that after the to_char() to cause it to generate multiple values? In all my born days I've never seen the like and me with no way to test it right now.gateleys wrote:If you want to apply the query in the source itself, use the condition below -Code: Select all
to_char(your_date_column, 'YYYYMMDD') BETWEEN '20090306' AND '20090312'
gateleys
Sarcasm? I was dead serious about never having seen syntax like that and would love to be able to try it on my own to see the behaviour first hand (something I would always do in the past) but being out of work at the moment puts a little damper on that.
Didn't say anything to the effect that I didn't think it would work. Maybe I should have said "play with it" rather than "test it".
Didn't say anything to the effect that I didn't think it would work. Maybe I should have said "play with it" rather than "test it".
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers