</p> mi two digits 60进制 显示值:455 O+ ~. {# F5 s/ `, s" }
Second:
9 e( k# Y+ s8 S) f' E) f ss two digits 60进制 显示值:252 C7 S y" q3 D2 {; a7 |5 {: {
其它
$ X1 P7 [7 U. ^$ Q Q digit 季度 显示值:4$ J; ]1 z5 q; K% \6 `- G
WW digit 昔时第几周 显示值:44
f" {1 S4 d! Z& O7 B$ w, k I: y W digit 当月第几周 显示值:1
8 ~/ C Y% d2 z1 |: I 24小时名目下时刻规模为: 0:00:00 - 23:59:59....
- N9 I3 t1 k% w$ j 12小时名目下时刻规模为: 1:00:00 - 12:59:59 ....0 C$ s6 \" p( K) ?
1. 日期和字符转换函数用法(to_date,to_char)) h5 ]: g0 f4 S0 ?" P7 N
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串
& x; x9 F3 N' F: @, j select to_char(sysdate,'yyyy') as nowYear from dual; //获取时刻的年8 z' F7 R- d. j8 w
select to_char(sysdate,'mm') as nowMonth from dual; //获取时刻的月% |! k, `6 J4 }1 N! K- z
select to_char(sysdate,'dd') as nowDay from dual; //获取时刻的日
6 i( R' O2 c! p7 Z. P( p select to_char(sysdate,'hh24') as nowHour from dual; //获取时刻的时
. G$ e! g. b& s* r6 x select to_char(sysdate,'mi') as nowMinute from dual; //获取时刻的分1 d- [4 u h% ]3 J2 r! }
select to_char(sysdate,'ss') as nowSecond from dual; //获取时刻的秒, j( P) Z% z# {2 C p2 Q
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual//9 `9 x6 n9 y8 o& B" {3 L
2.
% z8 r7 S' l! U3 [1 g1 D' w. ` select to_char( to_date(222,'J'),'Jsp') from dual, \7 P0 p6 T( M: s' d$ [3 e
显示Two Hundred Twenty-Two
M; n. _, D! L8 ? 3.求某天是礼拜几
4 r3 P+ ?% R0 |4 v% D select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;: M& s0 R" t' @9 L- K/ l {
礼拜一
. s+ W+ Y! h6 y2 m select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;. B, f9 U3 S3 l1 ^
monday
7 E8 g& H B/ ^9 t% W 设置日期说话
1 g4 s+ T& e" N ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';/ M& a+ H t" J z1 o/ w
也可以这样: Y( C' l/ J! f+ x% i; f% T) H
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
" d. Z5 g& U" m; L7 c 4. 两个日时代的天数
6 W1 F2 j% p0 k1 @ select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
$ w( B6 @* @+ {! E' h( M* j 5. 时刻为null的用法& E6 x# C; T2 H' K$ \ O$ ]+ @& V
select id, active_date from table1! h5 `9 j* M% y
UNION
( x, ^1 ^, [4 R select 1, TO_DATE(null) from dual;0 U- | j- D' e! {# U
注重要用TO_DATE(null)
& I$ u# {! |4 ?" s& l2 c9 ^- p+ ?) ^ 6.月份差
$ H8 U" s% ]' T& v' a a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')% T: D1 B g9 _; Q
那么12月31号午时12点之后和12月1号的12点之前是不包含在这个规模之内的。( N j3 M7 r/ S% S
所以,那时刻需要切确的时辰,感受to_char仍是需要的
a5 n" C$ J l, d) y& P% [4 O8 ]- b0 X 7. 日期名目冲突问题
* j B5 o4 ]" q; _ 输入的名目要看你安装的ORACLE字符集的类型, 好比: US7ASCII, date名目的类型就是: '01-Jan-01'
" N8 Y2 |* ]& {% e* c alter system set NLS_DATE_LANGUAGE = American
, Z! c1 Z6 I% _7 X1 ?' Z
5 }/ w6 ^5 f& P/ _ alter session set NLS_DATE_LANGUAGE = American |