Page 1 of 1
Logic Help
Posted: Tue Oct 08, 2013 5:44 pm
by 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
Posted: Tue Oct 08, 2013 7:09 pm
by 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.
Posted: Tue Oct 08, 2013 7:26 pm
by phanikumar
Source is from Oracle table and the format is in YYYY-MM-DD
Posted: Tue Oct 08, 2013 9:41 pm
by thirupathireddy.gankidi
Can you elaborate your question with some source data...
Posted: Tue Oct 08, 2013 9:58 pm
by 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.
Posted: Tue Oct 08, 2013 10:51 pm
by ray.wurlod
In exactly the same way you would do it with Oracle.
Contemplate using TO_DATE() functions around your dates in the query.
Posted: Tue Oct 08, 2013 10:53 pm
by 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?
Posted: Tue Oct 08, 2013 11:34 pm
by phanikumar
Running the query gives me the following format.
15-JUL-13
Posted: Tue Oct 08, 2013 11:51 pm
by 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.
Posted: Wed Oct 09, 2013 12:00 am
by 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.)
Posted: Wed Oct 09, 2013 1:27 am
by 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