场景:
create table TESTDATETIME
(
SZTIME VARCHAR2(128),
SZTIMESTAMP TIMESTAMP(6)
);
记录:
1 21/4/2014 08:20:23 AM
2 21/4/2014 08:20:23 PM
现在是要把 21/4/2014 08:20:23 AM 格式转化为20140421082023存储到SZTIMESTAMP字段中:
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'
alter session set nls_timestamp_format='yyyymmddhh24miss';
update testdatetime set sztimestamp=to_timestamp(sztime, 'dd/mm/yyyy hh:mi:ss am');
其中to_timestamp(sztime, 'dd/mm/yyyy hh:mi:ss am')的dd/mm/yyyy hh:mi:ss am为源数据格式,'yyyymmddhh24miss'为显示格式
如果需要报sztime字段也转换为yyyymmddhh24miss
update testdatetime set sztime=to_char(to_timestamp(sztime, 'dd/mm/yyyy hh:mi:ss am'));
select t.* from testdatetime t;
1 20140421082023 20140421082023
2 20140421202023 20140421202023
-----------------------------------------------------------------------------------------------------------
select to_timestamp(sztime, 'dd/mm/yyyy hh:mi:ss am') time from testdate t;