今天同事要查資料timestamp型別資料，要求轉換成date格式。

create or replace function convert_timestamp_to_date(tstamp timestamp) return date is

V_DATE date;

V_DAY VARCHAR2(2);

V_MONTH VARCHAR2(2);

V_YEAR VARCHAR2(4);

V_HH24MISS VARCHAR2(8);

BEGIN

V_DAY:=EXTRACT(DAY FROM tstamp);

V_MONTH:=EXTRACT(MONTH FROM tstamp);

V_YEAR:=EXTRACT(YEAR FROM tstamp);

SELECT TO_CHAR(tstamp,'hh24:mi:ss') INTO V_HH24MISS FROM DUAL;

V_DATE:= to_date(v_DAY || '-'|| V_MONTH || '-'|| V_YEAR||' '||V_HH24MISS,'DD-MM-YYYY HH24:MI:SS');

Return(V_DATE);

end;

Oracle timestamp型別轉換date格式指令碼（精確到秒）

相關文章