Combining Date and Time from Varchar to Timestamp

Seyed · Post by **Seyed** » Thu Apr 14, 2011 7:10 am

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

chulett · Post by **chulett** » Thu Apr 14, 2011 7:26 am

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

Seyed · Post by **Seyed** » Thu Apr 14, 2011 1:32 pm

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

chulett · Post by **chulett** » Thu Apr 14, 2011 2:11 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Apr 14, 2011 4:21 pm

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

Code: Select all

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

chulett · Post by **chulett** » Thu Apr 14, 2011 4:34 pm

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.

Seyed · Post by **Seyed** » Fri Apr 15, 2011 9:58 am

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

Seyed · Post by **Seyed** » Fri Apr 15, 2011 10:09 am

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

Nagaraj · Post by **Nagaraj** » Fri Apr 15, 2011 10:23 am

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.

Seyed · Post by **Seyed** » Fri Apr 15, 2011 11:55 am

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