Pinned topic How can I Convert Varchar to Date Time
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
How can I convert data type "20091918" to Oracle Date format "18/19/2009".
Updated on 2010-11-12T08:01:18Z at 2010-11-12T08:01:18Z by gongdanyan
gongdanyan 270003PA192 Posts
Re: How can I Convert Varchar to Date Time2010-11-12T08:01:18ZThis is the accepted answer. This is the accepted answer.iconv converts a string to an internal storage format, and oconv converts an expression to an output format
When using iconv to transform date, it will change date into a day number from a perticular date, eg.. if we want to change ‘01/30/2010’ to ’29-01-2010’, first
X = Iconv("01/30/2010", "D/E") X= 15371
Then we use oconv to transform X to needed format
X1 = Oconv( X,'D/DMYZ,Z,4' ) X1= 29/1/2010
‘/’ is the separator, ‘DMYZ,Z,4’ is the format of the date string, and any question can query help of transform stage, Using “D Code”.
Finally, we use DateGenericToODBC() in “DS Transform” to transform X1 into date which can be recognized by ODBC.
X2 = DateGenericToODBC(X1) X1= 2010-01-29
So, the final syntax is
DateGenericToODBC(Oconv(Iconv("01/30/2010", "D/E"),'D/DMYZ,Z,4' ))