Hi,
I am trying to convert a date format 6/1/2001 to Timestamp format(Oracle).
Can anyone suggest a solution for this scenario.
Thanks,
Sri.
Date to TImestamp conversion
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Actually, you want an Iconv function. This converts your date into DataStage internal format, and allows you to remove the MDY vs DMY ambiguity. For example: You can then convert this to an Oracle timestamp format using your own expression, the Transform from the SDK part of the Repository, or just feed the internal date through an ODBC stage (for example) where, if the data type is recorded as Timestamp, the generated SQL automatically includes a TO_DATE function.
Code: Select all
Iconv(TheDate, "DMDY")
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: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Basically there are two choices.
One is to use user-defined SQL, with the appropriate constant for the current date (SYSDATE, CURRENT_DATE and so on - it's different in each database - so much for standards!).
The other is to use DataStage system variables or functions.
The @DATE and @TIME system variables are set when the job starts and do not change while the job is running, so they are constant for every row.
The Date() and Time() functions access the system clock, so (potentially) change between rows.
Use Oconv() functions to construct the Timestamp.
One is to use user-defined SQL, with the appropriate constant for the current date (SYSDATE, CURRENT_DATE and so on - it's different in each database - so much for standards!).
The other is to use DataStage system variables or functions.
The @DATE and @TIME system variables are set when the job starts and do not change while the job is running, so they are constant for every row.
The Date() and Time() functions access the system clock, so (potentially) change between rows.
Use Oconv() functions to construct the Timestamp.
Code: Select all
Oconv(@DATE,"D-YMD[4,2,2]":" ":Oconv(@TIME,"MTS:")
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.