时间相关函数详解数据库

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; --日期转化为字符串    
select to_char(sysdate,'yyyy') as nowYear   from dual;   --获取时间的年    
select to_char(sysdate,'mm')    as nowMonth from dual;   --获取时间的月    
select to_char(sysdate,'dd')    as nowDay    from dual;  --获取时间的日    
select to_char(sysdate,'hh24') as nowHour   from dual;   --获取时间的时    
select to_char(sysdate,'mi')    as nowMinute from dual;  --获取时间的分    
select to_char(sysdate,'ss')    as nowSecond from dual;  --获取时间的秒  
     
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual;--字符转时间

select to_char( to_date(2222,'J'),'Jsp') from dual;--Two Thousand Two Hundred Twenty-Two

 select to_char(to_date('2019-03-28','yyyy-mm-dd'),'day') from dual; --星期四   

 select to_char(to_date('2019-03-28','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''') from dual;  --设置日期语言  

select floor(sysdate - to_date('20190327','yyyymmdd')) from dual; 

select id, active_date from table1       
   UNION       
   select 1, TO_DATE(null) from dual;  

 select count(*)       
   from ( select rownum-1 rnum       
       from all_objects       
       where rownum <= to_date('2019-02-28','yyyy-mm-dd') - to_date('2019-       
       02-01','yyyy-mm-dd')+1       
      )       
   where to_char( to_date('2019-02-01','yyyy-mm-dd')+rnum-1, 'D' )       
        not in ( '1', '7' )      

select months_between(to_date('2019-03-31','yyyy-MM-dd'),to_date（'2019-02-28','yyyy-MM-dd'))"MONTHS" FROM DUAL;      

select next_day(sysdate,2) from dual;

  SELECT EXTRACT(YEAR FROM TIMESTAMP '2019-03-27 02:38:40') from dual  

select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual   

select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual     

 select to_char( last_day( to_date('02'    | | to_char（sysdate,'yyyy'),'mmyyyy') ), 'dd' ) from dual

select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate       
   from dual;  

select TO_TIMESTAMP_TZ('2019-10-08 00:00:01','YYYY-MM-DD HH24:MI:SS') from dual;