DSXchange

Hello all,
I am trying to convert two columns (log_dt and log_tm) into a date-time column to load into an Oracle table. In the input file, log_dt is defined as varchar ( 10 ) and is in the 'mm/dd/yyyy' format. The log_tm column is defined as varchar ( 8 ) and is in the 'hh:mi:ss' format. Because the input file is a comma delimited file, the log_dt and log_tm columns are separated by a comma. The destination column in Oracle is defined as Timestamp (0). How do I combine and convert these two date and time column into a date-time column to load the result into an Oracle table? I have searched this forum, but haven't found the answer to this question.

Thank you in advance,

Seyed

Concatenation with a space between. That plus either IConv/OConv or substring to convert the date to YYYY-MM-DD format.

chulett wrote:Concatenation with a space between. That plus either IConv/OConv or substring to convert the date to YYYY-MM-DD format. ...

Hi Craig,
Thank you for your help. I tried the Oconv/Iconv that is listed below,

Oconv(Iconv(DSLink4.LOG_DT,"DYMD" ) + Iconv(DSLink4.LOG_TM,"MTHS"),"D-YMD[4,2,2]")

but, got

[ODBC Oracle Wire Protocol driver]Datetime field overflow. Error in parameter 30.

and I do have put a space before and one after the '+' sign used for concatenating LOG_DT and LOG_TM.

Thanks again,

Seyed

The plus sign does math, the concatenation operator in DataStage is a colon. And yes, you'll need the space.

And you'll need to specify a second argument for the Oconv() function applied to the date component, not the two components. Nor do you require a 12-hour clock in a timestamp, so lose the "H" from "MTHS".
So what you really need is something like

Sorry, didn't look close enough at your actual code. I don't think you actually have to convert the time at all, I meant just cat it on the end of the converted date.

chulett wrote:Sorry, didn't look close enough at your actual code. I don't think you actually have to convert the time at all, I meant just cat it on the end of the converted date. ...

Hi Craig,
I tried just concatenating the two columns as listed below,

DSLink4.LOG_DT : DSLink4.LOG_TM

but that caused the following error message:

[DataStage][SQL Client]Data has been truncated
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver]Datetime field overflow. Error in parameter 6.

and the date column in the director (parameter 6) had the following value:

LOG_DT_TM = " 4/10/2011 0:00:02"

Thank you for the help,

Seyed

ray.wurlod wrote:And you'll need to specify a second argument for the Oconv() function applied to the date component, not the two components. Nor do you require a 12-hour clock in a timestamp, so lose the "H" from "M ...

Hi Ray,
Because the target column in the Oracle table is defined as Timestamp(0), the maximum characters that I could put in this column is 19 characters.

IBM Support suggested that I do the following:

Oconv(Iconv(DSLink4.LOG_DT,"D"),"D2-") : " " : Oconv(Iconv(DSLink4.LOG_TM,"MT"),"MTS")

But this will extend the size to 20 characters which cause the truncation errors. I tried this just in case, and sure enough, I got the following error message:

[DataStage][SQL Client]Data has been truncated
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver]Datetime field overflow. Error in parameter 6.

and the value for parameter 6 in the Director was

LOG_DT_TM = "04-10-11 00:00:02"

IBM Support suggested the above solution because Timestamp(0) requires 'SYYYY-MM-DD HH24:MI:SS' format.

Thank you very much,

Seyed

this should work, i did the same thing it works.

Nagaraj wrote:

Code: Select all

 Oconv(Iconv(DSLink4.LOG_DT,"DMDY"),"D-YMD[4,2,2]") : " " : Oconv(Iconv(DSLink4.LOG_TM,"MT"),"MTS") 

this should work, i did the same thing it works.

Hi Nagaraj,
I tried your suggestion and it solved my problem.

Thank you very much,

Seyed