ALTER TABLE unit_bin_h ADD TRANS_TIME_TEMP DATE;
–处理中文 上午/下午
–UPDATE unit_bin_h SET TRANS_TIME_TEMP = TO_CHAR(TO_TIMESTAMP(trans_time, ‘dd-mon-rr hh.mi.ss.ff am’), ‘yyyy-MM-dd hh24:mi:ss’) WHERE TRANS_TIME LIKE ‘%下午’ OR TRANS_TIME LIKE ‘%上午’ ;
–处理英文 PM/AM
–UPDATE unit_bin_h SET TRANS_TIME_TEMP = TO_CHAR(TO_TIMESTAMP(trans_time,‘DD-MON-YY HH.MI.SS.FF AM’, ‘NLS_DATE_LANGUAGE=American’),‘yyyy-MM-dd hh24:mi:ss’) WHERE TRANS_TIME LIKE ‘%PM’ OR TRANS_TIME LIKE ‘%AM’ ;
ALTER TABLE unit_bin_h DROP COLUMN TRANS_TIME;
ALTER TABLE unit_bin_h RENAME COLUMN TRANS_TIME_TEMP TO TRANS_TIME;