oracle的日期和时间的处理

 

sql:
       with v as (select (e.hiredate+10/24/60/60 +20/24/60 +8/24 ) as hiredate from emp e)
       select  e.hiredate as 日期 ,
       to_char(e.hiredate ,'yyyy') 年,
       to_char(e.hiredate ,'mm') 月,
       to_char(e.hiredate ,'dd') 日,
       to_char(e.hiredate ,'hh24') 时,
       to_char(e.hiredate ,'mi') 分,
       to_char(e.hiredate ,'ss') 秒,
       to_char(e.hiredate ,'ddd') 年内第几天,    
       to_char(e.hiredate ,'day') 周几,  
       to_char(e.hiredate ,'yyyyMMddhh24miss') as 日期格式化,
       trunc(e.hiredate ,'dd') as 当天,
       trunc(e.hiredate,'day') as 周初,
       trunc(e.hiredate,'mm') as 月初,
       last_day(e.hiredate) as 月末,
       add_months(trunc(e.hiredate,'mm'),1) as 下月初,
       trunc(e.hiredate,'yy') as 年初,
       to_char(e.hiredate,'month') 月份 
       from v e

 --如果你想在你的数据库运行，把这句话with v as (select (e.hiredate+10/24/60/60 +20/24/60 +8/24 ) as hiredate from emp e) 换成  with v as (select sysdate as hiredate from dual)就可以了
 

结果如下：

 

 

 

一次性截不完，再来一行：