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;
原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/3975.html