Combining Date and Time from Varchar to Timestamp
Moderators: chulett, rschirm, roy
Combining Date and Time from Varchar to Timestamp
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
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
Hi Craig,chulett wrote:Concatenation with a space between. That plus either IConv/OConv or substring to convert the date to YYYY-MM-DD format. ...
Thank you for your help. I tried the Oconv/Iconv that is listed below,
but, gotOconv(Iconv(DSLink4.LOG_DT,"DYMD" ) + Iconv(DSLink4.LOG_TM,"MTHS"),"D-YMD[4,2,2]")
and I do have put a space before and one after the '+' sign used for concatenating LOG_DT and LOG_TM.[ODBC Oracle Wire Protocol driver]Datetime field overflow. Error in parameter 30.
Thanks again,
Seyed
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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")
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.
Hi Craig,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. ...
I tried just concatenating the two columns as listed below,
but that caused the following error message:DSLink4.LOG_DT : DSLink4.LOG_TM
and the date column in the director (parameter 6) had the following value:[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.
Thank you for the help,LOG_DT_TM = " 4/10/2011 0:00:02"
Seyed
Hi Ray,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 ...
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:
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:Oconv(Iconv(DSLink4.LOG_DT,"D"),"D2-") : " " : Oconv(Iconv(DSLink4.LOG_TM,"MT"),"MTS")
and the value for parameter 6 in the Director was[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.
IBM Support suggested the above solution because Timestamp(0) requires 'SYYYY-MM-DD HH24:MI:SS' format.LOG_DT_TM = "04-10-11 00:00:02"
Thank you very much,
Seyed
Code: Select all
Oconv(Iconv(DSLink4.LOG_DT,"DMDY"),"D-YMD[4,2,2]") : " " : Oconv(Iconv(DSLink4.LOG_TM,"MT"),"MTS")
Hi Nagaraj,Nagaraj wrote:this should work, i did the same thing it works.Code: Select all
Oconv(Iconv(DSLink4.LOG_DT,"DMDY"),"D-YMD[4,2,2]") : " " : Oconv(Iconv(DSLink4.LOG_TM,"MT"),"MTS")
I tried your suggestion and it solved my problem.
Thank you very much,
Seyed