Logic Help
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 60
- Joined: Tue Sep 20, 2011 10:44 pm
- Location: INDIA
Logic Help
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Most databases have a "current date" function or identifier that you can use in a query.
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: 60
- Joined: Tue Sep 20, 2011 10:44 pm
- Location: INDIA
-
- Participant
- Posts: 19
- Joined: Mon Aug 08, 2011 3:20 am
Can you elaborate your question with some source data...
Last edited by thirupathireddy.gankidi on Tue Oct 08, 2013 9:53 pm, edited 1 time in total.
-
- Participant
- Posts: 60
- Joined: Tue Sep 20, 2011 10:44 pm
- Location: INDIA
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Just to prove the format, can you please execute the query so that we can see the date format?
Code: Select all
SELECT EXPIRY_DATE FROM tablename WHERE ROWNUM <= 5
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: 60
- Joined: Tue Sep 20, 2011 10:44 pm
- Location: INDIA
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.
Last edited by chulett on Wed Oct 09, 2013 7:00 am, edited 1 time in total.
-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:
... then you need
so that the date formats coincide with what Oracle is returning.
(This is making some assumptions - see also Craig's email.)
Code: Select all
WHERE PREVIOUS_DATE >= '08-OCT-2013' AND FUTURE_DATE <= '09-FEB-2014'
(This is making some assumptions - see also Craig's email.)
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: 19
- Joined: Mon Aug 08, 2011 3:20 am