I have a 'Created_On' column in an Oracle table (column defined as 'Date') in which the date, when viewed, looks like MM/DD/YYCC HH:MM:SS. I'm retrieving records from this file.
I have a 'Last_Run_date'column in an Oracle table (column defined as 'Date') in which the date, when viewed, looks like MM/DD/YYCC HH:MM:SS.
The Oracle stages in the DS job define these two columns as 'Timestamp'.
I need to retrieve all records with a Created_On date > Last_Run_Date, so in my transform I have a table constraint of FileIn.CREATED_ON > ETL_Loadtime.LAST_RUN_DATE. Then I update the LAST_RUN_DATE with the current date using a derivation of Oconv(Date(), "D-YMD[4,2,2]") : " " : Oconv(Int(Time()), "MTS").
The look-up is failing, e.g. if LAST_RUN_DATE is 5/3/2004 11:06:05 AM I'm still getting records with a CREATED_ON date of 4/29/2004.
Oracle date comparison and timestamp
Moderators: chulett, rschirm, roy
DataStage has no concept of data types, everything, especially dates, are to be considered as string.
That being said, your constraint comparison is lexicographic, and because your format is incompatible with that type of comparison, you do not get the result you desire.
In DataStage, to compare dates, make sure you use the ISO standard of YYYY-MM-DD HH24:MI:SS. This way, any comparison (greater than, less than, equality, non-equality) works 100% of the time.
Ex:
1999-01-01 12:34:56 compare to 2000-12-31 12:34:56
will always work, because a left-to-right comparison will be doing apples-to-apples.
That being said, your constraint comparison is lexicographic, and because your format is incompatible with that type of comparison, you do not get the result you desire.
In DataStage, to compare dates, make sure you use the ISO standard of YYYY-MM-DD HH24:MI:SS. This way, any comparison (greater than, less than, equality, non-equality) works 100% of the time.
Ex:
1999-01-01 12:34:56 compare to 2000-12-31 12:34:56
will always work, because a left-to-right comparison will be doing apples-to-apples.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
So to compare an apple to an apple, my derivation should look like this?
OCONV(ICONV(linkname.CREATED_ON, "D-YMD[4,2,2]"), "D-YMD[4,2,2]") >
OCONV(ICONV(linkname.LAST_RUN_DATE, "D-YMD[4,2,2]"), "D-YMD[4,2,2]")
I added a sequential file stage to write the records to and I'm not getting any records now...
OCONV(ICONV(linkname.CREATED_ON, "D-YMD[4,2,2]"), "D-YMD[4,2,2]") >
OCONV(ICONV(linkname.LAST_RUN_DATE, "D-YMD[4,2,2]"), "D-YMD[4,2,2]")
I added a sequential file stage to write the records to and I'm not getting any records now...
The auto-generated SQL produced by the OCI stage for 8i and 9i slaps a TO_CHAR with formatting of YYYY-MM-DD HH24:MI:SS on every column defined as DATE or TIMESTAMP. If you're not using the OCI stage or you're not on 8i+ or you're using USER-DEFINED SQL of some nature, than Oracle will return a date in your customized locale.
I suggest you figure out which category you fall into and then make sure your queries return the data as you need it.
For the case of your supplied logic:
Your logic shows you internalizing a date in the form of D-YMD, but you stated your form is NOT this. Try just using a "D" catch-all to recognize the date. For your output conversion you have the right conversion.
You've probably noticed that it helps to set a universal standard throughout any ETL logic you write (shell scripts, SQL, DataStage, Informatica, whatever) to always present dates in the form YYYY-MM-DD HH24:MI:SS, it always works! Good luck.
I suggest you figure out which category you fall into and then make sure your queries return the data as you need it.
For the case of your supplied logic:
Code: Select all
OCONV(ICONV(linkname.CREATED_ON, "D-YMD[4,2,2]"), "D-YMD[4,2,2]") >
OCONV(ICONV(linkname.LAST_RUN_DATE, "D-YMD[4,2,2]"), "D-YMD[4,2,2]") You've probably noticed that it helps to set a universal standard throughout any ETL logic you write (shell scripts, SQL, DataStage, Informatica, whatever) to always present dates in the form YYYY-MM-DD HH24:MI:SS, it always works! Good luck.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Unfortunately I'm on DS version 5.2. I'm using ORACOCI8 stages for the two Oracle tables I'm reading the dates from. The generated SQL is for the dates is
TO_CHAR(linkname.LAST_RUN_DATE, 'YYYY-MM-DD HH24:MI:SS')
and
TO_CHAR(linkname.CREATED_ON, 'YYYY-MM-DD HH24:MI:SS')
When I said I 'viewed' the date as MM/DD/YYYY HH:MI:SS that was using a tool to view the data in the Oracle database - when I view the data from the DataStage sequential stage I'm testing with, the dates are in the format 'YYYY-MM-DD HH24:MI:SS'.
I'm so confused!
TO_CHAR(linkname.LAST_RUN_DATE, 'YYYY-MM-DD HH24:MI:SS')
and
TO_CHAR(linkname.CREATED_ON, 'YYYY-MM-DD HH24:MI:SS')
When I said I 'viewed' the date as MM/DD/YYYY HH:MI:SS that was using a tool to view the data in the Oracle database - when I view the data from the DataStage sequential stage I'm testing with, the dates are in the format 'YYYY-MM-DD HH24:MI:SS'.
I'm so confused!