How to use convert data type from Oracle data time string to Oracle varchar2

Tags:
Oracle
VARCHAR
DB version: Oracle 10gR2 10.2.0.4 64 bit
ogg version :11.2.1.0.1
Source db:
PARAMETER       VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE       AMERICAN
NLS_TERRITORY       AMERICA
NLS_CURRENCY       $
NLS_ISO_CURRENCY       AMERICA
NLS_NUMERIC_CHARACTERS       .,
NLS_CHARACTERSET       ZHS16GBK
NLS_CALENDAR       GREGORIAN
NLS_DATE_FORMAT       DD-MON-RR
NLS_DATE_LANGUAGE       AMERICAN
NLS_SORT       BINARY
NLS_TIME_FORMAT       HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY       $
NLS_COMP       BINARY
NLS_LENGTH_SEMANTICS       BYTE
NLS_NCHAR_CONV_EXCP       FALSE
NLS_NCHAR_CHARACTERSET       AL16UTF16
NLS_RDBMS_VERSION       10.2.0.4.0
 
target db:
PARAMETER       VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE       AMERICAN
NLS_TERRITORY       AMERICA
NLS_CURRENCY       $
NLS_ISO_CURRENCY       AMERICA
NLS_NUMERIC_CHARACTERS       .,
NLS_CHARACTERSET       ZHS16GBK
NLS_CALENDAR       GREGORIAN
NLS_DATE_FORMAT       DD-MON-RR
NLS_DATE_LANGUAGE       AMERICAN
NLS_SORT       BINARY
NLS_TIME_FORMAT       HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY       $
NLS_COMP       BINARY
NLS_LENGTH_SEMANTICS       BYTE
NLS_NCHAR_CONV_EXCP       FALSE
NLS_NCHAR_CHARACTERSET       AL16UTF16
NLS_RDBMS_VERSION       10.2.0.4.0
------insert test-----
source db:
insert into hdm.S_PFHTMDTL_QUERY_DAT_2(orgno,BUSIDATE,LSTDATE,TIMESTMP) values('123456789',sysdate,sysdate,to_char(to_timestamp(sysdate),'dd-mm-yy hh24:mi:ss'));
commit;
 
select orgno,BUSIDATE,LSTDATE,TIMESTMP from hdm.S_PFHTMDTL_QUERY_DAT_2;SQL> SQL> 
 
     ORGNO BUSIDATE LSTDATE  TIMESTMP
---------- -------- -------- ---------------------------------------------------------------------------
     12345 31-01-15 31-01-15 31-01-15 12.00.00.000000 AM
    123456 31-01-15 31-01-15 31-01-15 12.00.00.000000 AM
   1234567 31-01-15 31-01-15 31-01-15 12.00.00.000000 AM
 123456789 31-01-15 31-01-15 31-01-15 12.00.00.000000 AM
 
 
target db:
SQL> select orgno,azoneno,bzoneno,czoneno,BUSIDATE,LSTDATE,TIMESTMP from hdm.HDM_S_PFHTMDTL_QUERY_DAT;
     ORGNO AZONENO BZONENO     CZONENO  BUSIDATE   LSTDATE TIMESTMP
---------- -------------------- -------------------- -------------------- ---------- ---------- --------------------------
     12345 0000012345-10-0000 0000012345-10-0000   0000  2001-05-20 2001-05-20 2001-05-20 01:05:00X00
Convert result is incorrect.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

To convert data from Oracle data time string to Oracle varchar2, begin by normalizing your input strings to a standard format, such as YYYY:MM:DD HH:MM:SS. Once all of the data are standardized, you can go ahead and use the to_char function. This function changes a data time string into a character string. Use the code select ‘SELECT TO_DATE(”’ + CONVERT(VARCHAR(19), getdate(), 120), where 19 is the column location and 120 is the format of the date.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: