IT序号网

时间相关函数知识解答

lxf 2021年05月25日 数据库 163 0

1. 日期和字符转换函数用法(to_date,to_char)

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;--字符转时间

TO_DATE格式
        Year:     
        yy two digits 两位年                
        yyy three digits 三位年             
        yyyy four digits 四位年    
     
        Month:     
        mm    number     两位月              
        mon    abbreviated 字符集表示    
        month spelled out 字符集表示  
          
        Day:     
        dd    number         当月第几天 
        ddd    number         当年第几天
        dy    abbreviated 当周第几天简写   
        day    spelled out   当周第几天全写
        ddspth spelled out, ordinal twelfth 
             
        Hour:
        hh    two digits 12小时进制
        hh24 two digits 24小时进制 
              
        Minute:
        mi    two digits 60进制                显示值:45
              
        Second:
        ss    two digits 60进制                显示值:25
              
        其它
        Q     digit         季度                  显示值:4
        WW    digit         当年第几周            显示值:44
        W    digit          当月第几周            显示值:1
             
        24小时格式下时间范围为: 0:00:00 - 23:59:59....     
        12小时格式下时间范围为: 1:00:00 - 12:59:59 ....

 2、显示数字的英文

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

3、显示星期

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

 ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';     

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

ALBANIAN

sq

GENERIC_M

AMERICAN

us

binary

AMHARIC

am

GENERIC_M

ARABIC

ar

ARABIC

ARMENIAN

hy

GENERIC_M

ASSAMESE

as

binary

AZERBAIJANI

az

AZERBAIJANI

BANGLA

bn

binary

BELARUSIAN

be

RUSSIAN

BRAZILIAN PORTUGUESE

ptb

WEST_EUROPEAN

BULGARIAN

bg

BULGARIAN

CANADIAN FRENCH

frc

CANADIAN FRENCH

CATALAN

ca

CATALAN

CROATIAN

hr

CROATIAN

CYRILLIC KAZAKH

ckk

GENERIC_M

CYRILLIC SERBIAN

csr

GENERIC_M

CYRILLIC UZBEK

cuz

GENERIC_M

CZECH

cs

CZECH

DANISH

dk

DANISH

DARI

prs

GENERIC_M

DIVEHI

dv

GENERIC_M

DUTCH

nl

DUTCH

EGYPTIAN

eg

ARABIC

ENGLISH

gb

binary

ESTONIAN

et

ESTONIAN

FINNISH

sf

FINNISH

FRENCH

f

FRENCH

GERMAN DIN

din

GERMAN

GERMAN

d

GERMAN

GREEK

el

GREEK

GUJARATI

gu

binary

HEBREW

iw

HEBREW

HINDI

hi

binary

HUNGARIAN

hu

HUNGARIAN

ICELANDIC

is

ICELANDIC

INDONESIAN

in

INDONESIAN

IRISH

ga

binary

ITALIAN

i

WEST_EUROPEAN

JAPANESE

ja

binary

KANNADA

kn

binary

KHMER

km

GENERIC_M

KOREAN

ko

binary

LAO

lo

GENERIC_M

LATIN AMERICAN SPANISH

esa

SPANISH

LATIN BOSNIAN

lbs

GENERIC_M

LATIN SERBIAN

lsr

binary

LATIN UZBEK

luz

GENERIC_M

LATVIAN

lv

LATVIAN

LITHUANIAN

lt

LITHUANIAN

MACEDONIAN

mk

binary

MALAY

ms

MALAY

MALAYALAM

ml

binary

MALTESE

mt

GENERIC_M

MARATHI

mr

binary

MEXICAN SPANISH

esm

WEST_EUROPEAN

NEPALI

ne

GENERIC_M

NORWEGIAN

n

NORWEGIAN

ORIYA

or

binary

PERSIAN

fa

GENERIC_M

POLISH

pl

POLISH

PORTUGUESE

pt

WEST_EUROPEAN

PUNJABI

pa

binary

ROMANIAN

ro

ROMANIAN

RUSSIAN

ru

RUSSIAN

SIMPLIFIED CHINESE

zhs

binary

SINHALA

si

GENERIC_M

SLOVAK

sk

SLOVAK

SLOVENIAN

sl

SLOVENIAN

SPANISH

e

SPANISH

SWAHILI

sw

GENERIC_M

SWEDISH

s

SWEDISH

TAMIL

ta

binary

TELUGU

te

binary

THAI

th

THAI_DICTIONARY

TRADITIONAL CHINESE

zht

binary

TURKISH

tr

TURKISH

UKRAINIAN

uk

UKRAINIAN

VIETNAMESE

vn

VIETNAMESE

 4、计算日期差

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

5、 时间为null的用法

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

6、 查找2019-02-01至2019-02-28间除星期一和七的天数     

 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' )      

7、查找月份 

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

8、查找下个星期一

select next_day(sysdate,2) from dual;

9、extract()找出日期或间隔值的字段值

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

10、处理月份天数不定的办法    

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

11、找出今年的天数

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

12、闰年的处理方法(判断2月是否是28)

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

13、不同时区的处理 

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

 14、字符串转时间戳

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

发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

oracle 查询之前的表数据知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。