Logic Help

[phanikumar]

Hi All,

I have a scenario where I need to get data between two date values.

start date is todays date 2013-10-09
Future date is 2014-02-12

I need to output all the records between these two dates.

I've tried using (>= and <==) but doesn't work.

Any suggestions.

Regards
Kumar

[ray.wurlod]

Not enough information. "Get" from where, or within a DataStage job? If the source is a database table, you will need to make sure that the date picture (format) in your query matches the default used for the database, or make appropriate changes (such as conversion functions or connection options) so that the format in the query is correct.

Most databases have a "current date" function or identifier that you can use in a query.

[phanikumar]

Source is from Oracle table and the format is in YYYY-MM-DD

[thirupathireddy.gankidi]

Can you elaborate your question with some source data...

[phanikumar]

I have my source data consists of a column EXPIRY_DATE which has got all sorts of current past and future dates.

This is a daily process and for each run I need to select the records with the EXPIRY_DATE ranging between 1day minus current date upto 4Months from current date.

Sample scenario:

For todays run I need to select data as below

Previous Date: 2013-10-08

FUTURE DATE: 2014-02-09 ( 4Months from current date)

So I need to select all the records between these two dates.

I've done this using Oracle, can you let me know how can this be acheived in Datastage.

[ray.wurlod]

In exactly the same way you would do it with Oracle.

Contemplate using TO_DATE() functions around your dates in the query.

[ray.wurlod]

Just to prove the format, can you please execute the query

Code: Select all

SELECT EXPIRY_DATE FROM tablename WHERE ROWNUM <= 5 

so that we can see the date format?

[phanikumar]

Running the query gives me the following format.

15-JUL-13

[chulett]

Knowing where you 'ran the query' will make a difference. Anything other than sqlplus will format the date for you... assuming this is a DATE and not a VARCHAR2 or CHAR field. Absent of any formatting by you in the select, sqlplus will just use the default NLS_DATE_FORMAT. And again if they are DATE fields, they can be directly compared... so can dates stored as a string if they are in the proper format, that is any variant of YMD.

[ray.wurlod]

... then you need

Code: Select all

WHERE PREVIOUS_DATE >= '08-OCT-2013' AND FUTURE_DATE <= '09-FEB-2014'

so that the date formats coincide with what Oracle is returning.

(This is making some assumptions - see also Craig's email.)

[thirupathireddy.gankidi]

Parametrise Previous Date,FUTURE DATE in your job and add below constraint in transformer.
EXPIRY_DATE>= Previous Date and EXPIRY_DATE<= FUTURE DATE