Date to TImestamp conversion

appcon · Post by **appcon** » Thu Jul 15, 2004 3:04 pm

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.

ketfos · Post by **ketfos** » Thu Jul 15, 2004 3:16 pm

Hi,
You can use the oconv function to change the date to internal format.
Then pass this to Timestamp function to get the date with time.

Ketfos

ray.wurlod · Post by **ray.wurlod** » Fri Jul 16, 2004 3:52 am

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:

Code: Select all

Iconv(TheDate, "DMDY")

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.

appcon · Post by **appcon** » Fri Jul 16, 2004 2:02 pm

Thanks Ray & Ketfos

The suggestions worked like a charm. Now I need to figure out how to generate sysdate in datastage. Any suggestions for this.

Srikanth.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 16, 2004 4:47 pm

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.

Code: Select all

Oconv(@DATE,"D-YMD[4,2,2]":" ":Oconv(@TIME,"MTS:")