getting rows between two dates

[srao]

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.

[betterthanever]

CAN YOU BE MORE SPECIFIC??

[girija]

Please tell us about your input and expected ouput with a simple example....

[Pagadrai]

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.

[ray.wurlod]

Do you want to generate these or do you want to retrieve them from some data source? On a stream input or a reference input link?

[srao]

[quote="ray.wurlod"]Do you want to generate these or do you want to retrieve them from some data source? On a stream input or a reference input link?[/quote]

I need to generate these row through start date and enddate

oracle is source and SQL server is target database

[gateleys]

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'

If you want to achieve this in DataStage, use the constraint below in your Transformer -

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'

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.

[ray.wurlod]

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.

[chulett]

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'

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.

[Sainath.Srinivasan]

Do you have any "Date" or "Calendar" table in oracle ?

It will be better to generate rows in Oracle itself.

Create a view to generate rows for last n years. You can join this in Oracle source.

[gateleys]

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'

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.

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]

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".