I am facinig some problem with Conversion of string to timestamp
my input is Sequential file and i am loading(Append) data into existingOracle table.
Seq--->Trans----> Oracle Table
I am not able to view the date..
I am getting warning like :
Transformer_1,0: Conversion error calling conversion routine timestamp_from_string data may have been lost.
My input dates are like 19891025(YYYYMMDD,string format) and i want to load timestamp format in oracle table
i tried with the function StringToTimestamp(DSLink2.MBR_DOB_DT,"%yyyy-%mm-%dd %hh:%nn:%ss")
But i am not getting the timestamp..
Please give me idea what can i put in transformer
Thanks,
Reddy
TimeStamp Conversion
Moderators: chulett, rschirm, roy
You won't get a timestamp unless your format string matches your data. Try formatting with '%yyyy%mm%dd %hh:%nn:%ss' and making sure your input string also has a dummy time part added to it.
i.e.
i.e.
Code: Select all
StringToTimestamp(DSLink2.MBR_DOB_DT:" 00:00:00","%yyyy%mm%dd %hh:%nn:%ss")
Re: TimeStamp Conversion
If your data is like 20071027 023010 (yyyymmdd hhnnss format)
use following:
StringToTimestamp(dslink.col[1,4]:'-':dslink.col[5,2]:'-':dslink.col[7,2]:' ':dslink.col[11,2]:':':dslink.col[13,2]:':':dslink.col[15,2], "%yyyy-%mm-%dd %hh:%nn:%ss)
If your data is like 2007-10-27 02:30:10
use following;
StringToTimestamp(dslink.col, "%yyyy-%mm-%dd %hh:%nn:%ss)
use following:
StringToTimestamp(dslink.col[1,4]:'-':dslink.col[5,2]:'-':dslink.col[7,2]:' ':dslink.col[11,2]:':':dslink.col[13,2]:':':dslink.col[15,2], "%yyyy-%mm-%dd %hh:%nn:%ss)
If your data is like 2007-10-27 02:30:10
use following;
StringToTimestamp(dslink.col, "%yyyy-%mm-%dd %hh:%nn:%ss)