SELECT hiredate,
       to_number(to_char(hiredate, ‘hh24‘)) 时,
       to_number(to_char(hiredate, ‘mi‘)) 分,
       to_number(to_char(hiredate, ‘ss‘)) 秒,
       to_number(to_char(hiredate, ‘dd‘)) 日,
       to_number(to_char(hiredate, ‘mm‘)) 月,
       to_number(to_char(hiredate, ‘yyyy‘)) 年,
       to_number(to_char(hiredate, ‘ddd‘)) 年内第几天,
       trunc(hiredate, ‘dd‘) 一天之始,
       trunc(hiredate, ‘day‘) 周初,
       trunc(hiredate, ‘mm‘) 月初,
       last_day(hiredate) 月未,
       add_months(trunc(hiredate, ‘mm‘),1) 下月初,
       trunc(hiredate, ‘yy‘) 年初,
       to_char(hiredate, ‘day‘) 周几,
       to_char(hiredate, ‘month‘) 月份
  FROM (SELECT hiredate + 30/24/60/60 + 20/24/60 + 5/24 AS hiredate FROM emp WHERE ROWNUM <=1);

本文出自 “专注于Oracle性能调优” 博客，谢绝转载！

常用的日期格式