多种日期格式:
YYYY:四位表示的年份
YYY,YY,Y:年份的最后三位、两位或一位,缺省为当前世纪
MM:01~12的月份编号
MONTH:九个字符表示的月份,右边用空格填补
MON:三位字符的月份缩写
WW:一年中的星期
D:星期中的第几天
DD:月份中的第几天
DDD:年所中的第几天
DAY:九个字符表示的天的全称,右边用空格补齐
HH,HH12:一天中的第几个小时,12进制表示法
HH24:一天中的第几个小时,取值为00~23
MI:一小时中的分钟
SS:一分钟中的秒
SSSSS:从午夜开始过去的秒数
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
一。基本运算部分
A)to_char函数
1. 年的运算
1.1 Y或YY或YYY 年的最后一位,两位或三位,YYYY 四位年份,IYYY ISO标准的四位年份
SQL> Select to_char(sysdate,'Y'),to_char(sysdate,'YY'),to_char(sysdate,'YYY'),to_char(sysdate,'YYYY'),TO_CHAR(SYSTIMESTAMP, 'IYYY') from dual;
TO_CHAR(SYSDATE,'Y') TO_CHAR(SYSDATE,'YY') TO_CHAR(SYSDATE,'YYY') TO_CHAR(SYSDATE,'YYYY') TO_CHAR(SYSTIMESTAMP,'IYYY')
-------------------- --------------------- ---------------------- ----------------------- ----------------------------
9 09 009 2009 2009
1.2 YEAR
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'YEAR') from dual;
TO_CHAR(SYSTIMESTAMP,'YEAR')
------------------------------------------
TWO THOUSAND NINE
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'YEAR','NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(SYSTIMESTAMP,'YEAR','N
------------------------------------------
TWO THOUSAND NINE
2.Q 季度 1~3月为第一季度,2表示第二季度。
SQL> Select to_char(sysdate,'Q') from dual;
TO_CHAR(SYSDATE,'Q')
--------------------
1
3. 月份数
3.1 MM 月份数
SQL> Select to_char(sysdate,'MM') from dual;
TO_CHAR(SYSDATE,'MM')
---------------------
02
3.2 Month 用9个字符长度表示的月份名
SQL> Select to_char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------
2月
3.3 月份函数汇总:
SQL> Select to_char(sysdate,'Month'),
2 to_char(sysdate,'mon'),
3 to_char(sysdate,'Month'),
4 to_char(sysdate,'mon','NLS_DATE_LANGUAGE = American'), --三位字符的月份缩写全部小写
5 to_char(sysdate,'Mon','NLS_DATE_LANGUAGE = American'), --三位字符的月份缩写首字母大些
6 to_char(sysdate,'MON','NLS_DATE_LANGUAGE = American'), --三位字符的月份缩写全部大些
7 to_char(sysdate,'month','NLS_DATE_LANGUAGE = American'), --九个字符表示的月份,右边用空格填补全部小写
8 to_char(sysdate,'Month','NLS_DATE_LANGUAGE = American'), --九个字符表示的月份,右边用空格填补首字母大些
9 to_char(sysdate,'MONTH','NLS_DATE_LANGUAGE = American') --九个字符表示的月份,右边用空格填补全部大些
10 FROM dual;
TO_CHAR(SYSDATE,'MONTH') TO_CHAR(SYSDATE,'MON') TO_CHAR(SYSDATE,'MONTH') TO_CHAR(SYSDATE,'MON','NLS_DAT TO_CHAR(SYSDATE,'MON','NLS_DAT TO_CHAR(SYSDATE,'MON','NLS_DAT TO_CHAR(SYSDATE,'MONTH','NLS_D TO_CHAR(SYSDATE,'MONTH','NLS_D TO_CHAR(SYSDATE,'MONTH','NLS_D
------------------------ ---------------------- ------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
2月 2月 2月 feb Feb FEB february February FEBRUARY
3.4.RM 月份的罗马表示 (V在罗马数字中表示 5)
SQL> Select to_char(sysdate,'RM') from dual;
TO_CHAR(SYSDATE,'RM')
---------------------
II
4 周的运算
4.1 WW 当年第几周 (2009年2月27日为2009年的第9周)
SQL> Select to_char(sysdate,'WW') from dual;
TO_CHAR(SYSDATE,'WW')
---------------------
09
SQL>
4.2 W 本月第几周 (2009年2月27日为2月第4周)
SQL> Select to_char(sysdate,'W') from dual;
TO_CHAR(SYSDATE,'W')
--------------------
4
6. 天的运算
6.1 DDD 当年第几天 (2009年2月27日为2009年第58天)
SQL> Select to_char(sysdate,'DDD') from dual;
TO_CHAR(SYSDATE,'DDD')
----------------------
058
6.2 DD 当月第几天 (2009年2月27日为2月的第27天)
SQL> Select to_char(sysdate,'DD') from dual;
TO_CHAR(SYSDATE,'DD')
---------------------
27
6.3 D 周内第几天 (2009年2月27日为这一周的第6天,周五)
数据字显示和星期对应关系如下)
1 2 3 4 5 6 7
日 一 二 三 四 五 六
SQL> Select to_char(sysdate,'D') from dual;
TO_CHAR(SYSDATE,'D')
--------------------
6
6.4 DAY 中文(英文)的星期几 ( 2009年2月27日为星期五)
SQL> Select to_char(sysdate,'DAY') from dual;
TO_CHAR(SYSDATE,'DAY')
----------------------
星期五
6.5 DY 中文(英文)的星期几 ( 2009年2月27日为星期五)
SQL> Select to_char(sysdate,'DY') from dual;
TO_CHAR(SYSDATE,'DY')
---------------------
星期五
6.6 日函数运算汇总:
SQL> Select
2 to_char(sysdate,'D'),
3 to_char(sysdate,'DD'),
4 to_char(sysdate,'DDD'),
5 to_char(sysdate,'dy','NLS_DATE_LANGUAGE = American'),
6 to_char(sysdate,'Dy','NLS_DATE_LANGUAGE = American'),
7 to_char(sysdate,'DY','NLS_DATE_LANGUAGE = American'),
8 to_char(sysdate,'day','NLS_DATE_LANGUAGE = American'),
9 to_char(sysdate,'Day','NLS_DATE_LANGUAGE = American'),
10 to_char(sysdate,'DAY','NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(SYSDATE,'D') TO_CHAR(SYSDATE,'DD') TO_CHAR(SYSDATE,'DDD') TO_CHAR(SYSDATE,'DY','NLS_DATE TO_CHAR(SYSDATE,'DY','NLS_DATE TO_CHAR(SYSDATE,'DY','NLS_DATE TO_CHAR(SYSDATE,'DAY','NLS_DAT TO_CHAR(SYSDATE,'DAY','NLS_DAT TO_CHAR(SYSDATE,'DAY','NLS_DAT
-------------------- --------------------- ---------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
6 27 058 fri Fri FRI friday Friday FRIDAY
SQL> Select
2 to_char(sysdate,'D')||chr(10)||
3 to_char(sysdate,'DD')||chr(10)||
4 to_char(sysdate,'DDD')||chr(10)||
5 to_char(sysdate,'dy','NLS_DATE_LANGUAGE = American')||chr(10)||
6 to_char(sysdate,'Dy','NLS_DATE_LANGUAGE = American')||chr(10)||
7 to_char(sysdate,'DY','NLS_DATE_LANGUAGE = American')||chr(10)||
8 to_char(sysdate,'day','NLS_DATE_LANGUAGE = American')||chr(10)||
9 to_char(sysdate,'Day','NLS_DATE_LANGUAGE = American')||chr(10)||
10 to_char(sysdate,'DAY','NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(SYSDATE,'D')||CHR(10)|
--------------------------------------------------
6
27
058
fri
Fri
FRI
friday
Friday
FRIDAY
7 小时的运算
7.1.HH或HH12 12进制小时数 (16:09分为用12小时制计时为4点)
SQL> Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate,'HH') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2 TO_CHAR(SYSDATE,'HH')
------------------------------ ---------------------
2009-02-28 10:58:54 10
7.2.HH24 24小时制
SQL> Select to_char(sysdate+0.5,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+0.5,'HH'),to_char(sysdate+0.5,'HH24') from dual;
TO_CHAR(SYSDATE+0.5,'YYYY-MM-D TO_CHAR(SYSDATE+0.5,'HH') TO_CHAR(SYSDATE+0.5,'HH24')
------------------------------ ------------------------- ---------------------------
2009-02-28 22:59:49 10 22
8 上下午的运算
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'AM','NLS_DATE_LANGUAGE = American'),TO_CHAR(SYSTIMESTAMP, 'AM'),
2 TO_CHAR(SYSTIMESTAMP, 'PM','NLS_DATE_LANGUAGE = American'),TO_CHAR(SYSTIMESTAMP, 'PM'),
3 TO_CHAR(SYSTIMESTAMP+0.5, 'PM','NLS_DATE_LANGUAGE = American'),TO_CHAR(SYSTIMESTAMP+0.5, 'PM') from dual;
TO_CHAR(SYSTIMESTAMP,'AM','NLS TO_CHAR(SYSTIMESTAMP,'AM') TO_CHAR(SYSTIMESTAMP,'PM','NLS TO_CHAR(SYSTIMESTAMP,'PM') TO_CHAR(SYSTIMESTAMP+0.5,'PM', TO_CHAR(SYSTIMESTAMP+0.5,'PM')
------------------------------ -------------------------- ------------------------------ -------------------------- ------------------------------ ------------------------------
AM 上午 AM 上午 PM 下午
9 MI:一小时中的分钟 Minute (0-59).
SQL> Select to_char(sysdate,'mi') from dual;
TO_CHAR(SYSDATE,'MI')
---------------------
54
10 秒的运算
10.1 SS:一分钟中的秒 ,SSSSs:从午夜开始过去的秒数
SQL> Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate,'ss'),to_char(sysdate,'SSSSS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2 TO_CHAR(SYSDATE,'SS') TO_CHAR(SYSDATE,'SSSSS')
------------------------------ --------------------- ------------------------
2009-02-28 11:06:04 04 39964
10.2 这个好像取到6位就行了
SQL> SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||chr(10)||
2 TO_CHAR(SYSTIMESTAMP, 'SS.FF1')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF2')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF3')||chr(10)||
3 TO_CHAR(SYSTIMESTAMP, 'SS.FF4')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF5')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF6')||chr(10)||
4 TO_CHAR(SYSTIMESTAMP, 'SS.FF7')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF8')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF9') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
--------------------------------------------------------------------------------
2009-02-28 11:14:21
21.8
21.81
21.810
21.8100
21.81002
21.810022
21.8100220
21.81002200
21.810022000
B)trunc()函数,都是对数据向前取整
1. 计算小时,分,秒,毫秒
SQL> SELECT DAYS,
2 A,
3 TRUNC(A * 24) HOURS,
4 TRUNC(A * 24 * 60 - 60 * TRUNC(A * 24)) MINUTES,
5 TRUNC(A * 24 * 60 * 60 - 60 * TRUNC(A * 24 * 60)) SECONDS,
6 TRUNC(A * 24 * 60 * 60 * 100 - 100 * TRUNC(A * 24 * 60 * 60)) MSECONDS
7 FROM (SELECT TRUNC(SYSDATE) DAYS, SYSDATE - TRUNC(SYSDATE) A FROM DUAL);
DAYS A HOURS MINUTES SECONDS MSECONDS
----------- ---------- ---------- ---------- ---------- ----------
2009-2-28 0.56979166 13 40 30 0
2. 世纪第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'cc') from dual;
NOW TRUNC(SYSDATE,'CC')
------------------- -------------------
2009-02-28 13:51:30 2001-1-1
3. 年的第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,
2 trunc(SYSDATE,'SYYYY'), trunc(SYSDATE,'YYYY'),
3 trunc(SYSDATE,'YEAR'), trunc(SYSDATE,'SYEAR'),
4 trunc(SYSDATE,'YYY'), trunc(SYSDATE,'YY'), trunc(SYSDATE,'Y')
5 from dual;
NOW TRUNC(SYSDATE,'SYYYY') TRUNC(SYSDATE,'YYYY') TRUNC(SYSDATE,'YEAR') TRUNC(SYSDATE,'SYEAR') TRUNC(SYSDATE,'YYY') TRUNC(SYSDATE,'YY') TRUNC(SYSDATE,'Y')
------------------- ---------------------- --------------------- --------------------- ---------------------- -------------------- ------------------- ------------------
2009-02-28 13:52:51 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1
4. 季度第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'q'),trunc(SYSDATE+100,'q')from dual;
NOW TRUNC(SYSDATE,'Q') TRUNC(SYSDATE+100,'Q')
------------------- ------------------ ----------------------
2009-02-28 14:07:03 2009-1-1 2009-4-1
5. 月份的第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'MONTH'),trunc(SYSDATE,'MON'),trunc(SYSDATE,'MM'),trunc(SYSDATE,'RM') from dual;
NOW TRUNC(SYSDATE,'MONTH') TRUNC(SYSDATE,'MON') TRUNC(SYSDATE,'MM') TRUNC(SYSDATE,'RM')
------------------- ---------------------- -------------------- ------------------- -------------------
2009-02-28 14:09:08 2009-2-1 2009-2-1 2009-2-1 2009-2-1
6 周
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'WW'),trunc(SYSDATE,'W')from dual;
NOW TRUNC(SYSDATE,'WW') TRUNC(SYSDATE,'W')
------------------- ------------------- ------------------
2009-02-28 14:13:43 2009-2-26 2009-2-22
6.2 周的第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'DAY'),trunc(SYSDATE,'DY'),trunc(SYSDATE,'D') from dual;
NOW TRUNC(SYSDATE,'DAY') TRUNC(SYSDATE,'DY') TRUNC(SYSDATE,'D')
------------------- -------------------- ------------------- ------------------
2009-02-28 14:24:31 2009-2-22 2009-2-22 2009-2-22
9. 天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'DDD'),trunc(SYSDATE,'DD'),trunc(SYSDATE,'J') from dual;
NOW TRUNC(SYSDATE,'DDD') TRUNC(SYSDATE,'DD') TRUNC(SYSDATE,'J')
------------------- -------------------- ------------------- ------------------
2009-02-28 14:21:41 2009-2-28 2009-2-28 2009-2-28
10 小时
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'HH'),trunc(SYSDATE,'HH12'),trunc(SYSDATE,'HH24') from dual;
NOW TRUNC(SYSDATE,'HH') TRUNC(SYSDATE,'HH12') TRUNC(SYSDATE,'HH24')
------------------- ------------------- --------------------- ---------------------
2009-02-28 14:26:39 2009-2-28 14:00:00 2009-2-28 14:00:00 2009-2-28 14:00:00
11 分
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'MI') from dual;
NOW TRUNC(SYSDATE,'MI')
------------------- -------------------
2009-02-28 14:31:37 2009-2-28 14:31:00
C) round()函数 和trunc函数类似,部分取值向后取整
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'cc') from dual;
NOW ROUND(SYSDATE,'CC')
------------------- -------------------
2009-02-28 14:33:10 2001-1-1
SQL> select round(SYSDATE,'cc') from dual;
ROUND(SYSDATE,'CC')
-------------------
2001-1-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,
2 round(SYSDATE,'SYYYY'), round(SYSDATE,'YYYY'),
3 round(SYSDATE,'YEAR'), round(SYSDATE,'SYEAR'),
4 round(SYSDATE,'YYY'), round(SYSDATE,'YY'), round(SYSDATE,'Y')
5 from dual;
NOW ROUND(SYSDATE,'SYYYY') ROUND(SYSDATE,'YYYY') ROUND(SYSDATE,'YEAR') ROUND(SYSDATE,'SYEAR') ROUND(SYSDATE,'YYY') ROUND(SYSDATE,'YY') ROUND(SYSDATE,'Y')
------------------- ---------------------- --------------------- --------------------- ---------------------- -------------------- ------------------- ------------------
2009-02-28 14:33:11 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'Q')from dual;
NOW ROUND(SYSDATE,'Q')
------------------- ------------------
2009-02-28 14:33:11 2009-4-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'q'),round(SYSDATE+100,'q')from dual;
NOW ROUND(SYSDATE,'Q') ROUND(SYSDATE+100,'Q')
------------------- ------------------ ----------------------
2009-02-28 14:33:11 2009-4-1 2009-7-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'MONTH'),round(SYSDATE,'MON'),round(SYSDATE,'MM'),round(SYSDATE,'RM') from dual;
NOW ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'MON') ROUND(SYSDATE,'MM') ROUND(SYSDATE,'RM')
------------------- ---------------------- -------------------- ------------------- -------------------
2009-02-28 14:33:11 2009-3-1 2009-3-1 2009-3-1 2009-3-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'WW'),round(SYSDATE,'W')from dual;
NOW ROUND(SYSDATE,'WW') ROUND(SYSDATE,'W')
------------------- ------------------- ------------------
2009-02-28 14:33:11 2009-2-26 2009-3-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'DDD'),round(SYSDATE,'DD'),round(SYSDATE,'J') from dual;
NOW ROUND(SYSDATE,'DDD') ROUND(SYSDATE,'DD') ROUND(SYSDATE,'J')
------------------- -------------------- ------------------- ------------------
2009-02-28 14:33:11 2009-3-1 2009-3-1 2009-3-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'DAY'),round(SYSDATE,'DY'),round(SYSDATE,'D') from dual;
NOW ROUND(SYSDATE,'DAY') ROUND(SYSDATE,'DY') ROUND(SYSDATE,'D')
------------------- -------------------- ------------------- ------------------
2009-02-28 14:33:11 2009-3-1 2009-3-1 2009-3-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'HH'),round(SYSDATE,'HH12'),round(SYSDATE,'HH24') from dual;
NOW ROUND(SYSDATE,'HH') ROUND(SYSDATE,'HH12') ROUND(SYSDATE,'HH24')
------------------- ------------------- --------------------- ---------------------
2009-02-28 14:33:11 2009-2-28 15:00:00 2009-2-28 15:00:00 2009-2-28 15:00:00
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'MI') from dual;
NOW ROUND(SYSDATE,'MI')
------------------- -------------------
2009-02-28 14:33:11 2009-2-28 14:33:00
D)TO_DATE使用详解
时常使用to_date函数来查询特定时间内的数据。
语法:
TO_DATE(x [, format])用于将字符串x转换为时间值,该函数可以指定一个可选的字符串format参数来说明x的格式。
如果没有指定format参数,日期就使用默认的数据库格式(通常是DD-MON-YYYY或DD-MON-YY)。
注意:
数据库参数NLS_DATE_FORMAT指定了默认的日期格式。可以修改NLS_DATE_FORMAT的设置。
2. 指定时间
当然,在时间值中也可以指定时间。如果在时间值中没有指定时间,那么时间值的时间部分默认为12:00:00 A.M.。时间可以使用前面表5-2所示的各种格式。时间格式的一个例子是HH24:MI:SS,其中:
● HH24是一个2位数字,表示24小时格式中的小时数,该值的范围为00~23。
● MI 是一个2位数字,表示分钟,该值的范围为00~59。
● SS 是一个2位数字,表示秒,该值的范围为00~59。
使用HH24:MI:SS格式的一个时间的例子是19:23:36。使用这个时间的一个完整时间值是:
05-FEB-1968 19:32:36
这个时间值的格式为:
DD-MON-YYYY HH24:MI:SS
to_data(date&time,format)
其中第一个参数date&time是我们要查询的具体日期和时间,比如:2006年5月21日。但是这个时间也分
为很多种精度的,比如:
2006年5月21日
2006年5月21日17:24
2006年5月21日17:34分55秒
这个精度要根据实际情况来定。
另外一个参数format决定的日期的表示方法,比如:
2006年5月21日 可以表示为:060521、20060521、2006FEB21等等
而他们对应的format为
060521 YYMMDD
20060521 YYYYMMDD
2006FEB21 YYYYMONDD
示例:
select * from book where intime=to_date('2003/02/17/17/53/55','YYYY/MM/DD/HH24/MI/SS')
上面的语句是查询:2003年8月17日下午5点53分55秒,入库的书本。
其实用下面的写法也是可以的
select * from book where intime=to_date('20030217175355','YYYYMMDDHH24MISS')
select * from book where intime=to_date
('2003-02-17-17-53-55','YYYY-MM-DD-HH24/-I-SS')
中间的连字符"/"、":"或是"-",并不影响表达,只是为了更加容易看清楚。
表示年份有以下几种格式,及这种格式对应的表达方法:
2003年为例
YYYY 2003
YYY 003
YY 03
月份表示格式及表达方法:
二月分为例
MM 08
RM IIX(罗马数字)
MONTH february
MON feb
日期的格式及表达方法:
17号为例
DDD 76 2月的17日在本年度(不是闰年的情况)是多少天
DD 17 在本月中是号(17号)
D 在这一个星期是哪天
星期的格式和表示方法:
星期一为例
DAY monday 全名显示
DY mon 缩写
小时的格式和表示方法:
HH24 18 二十四小时制
分钟的格式和表示方法:
MI 32 该小时32分钟
秒的表示方法
SS 28 该分钟28秒
二。 其他运算函数
1、add_months()用于从一个日期值增加或减少一些月份
SQL> select add_months(sysdate,-12) "Last Year ",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,add_months(sysdate,12) "Next Year " from dual;
Last Year NOW Next Year
------------------ ------------------- ------------------
2008-2-29 14:41:54 2009-02-28 14:41:54 2010-2-28 14:41:54
2、current_date()返回当前会放时区中的当前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;
SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11月-03
SQL> alter session set time_zone='-11:00'
2 /
会话已更改。
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.59.13.668000 下午 -11:00
SQL>
3、current_timestamp()以timestamp with time zone数据类型返回当前会放时区中的当前日期
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
+08:00 13-11月-03 11.56.28.160000 上午 +08:00
SQL> alter session set time_zone='-11:00'
2 /
会话已更改。
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.58.00.243000 下午 -11:00
4、dbtimezone()返回时区
varchar_value:=dbtimezone
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+08:00
5、extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;
This Month
----------
2
SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;
3 Years Out
-----------
2012
6、last_day()返回包含了日期参数的月份的最后一天的日期
date_value:=last_day(date_value)
SQL> select last_day(sysdate) "Last day of this month" from dual;
Last day of this month
----------------------
2009-2-28 14:54:09
7、localtimestamp()返回会话中的日期和时间
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
----------------------------
28-2月 -09 02.54.55.266868 下午
SQL> select localtimestamp,current_timestamp from dual;
LOCALTIMESTAMP CURRENT_TIMESTAMP
---------------------------- ------------------------------------
28-2月 -09 02.56.39.923571 下午 28-2月 -09 02.56.39.923571 下午 +08:00
8、months_between()判断两个日期之间的月份数量
number_value:=months_between(date_value,date_value)
SQL> select months_between(sysdate,date'1971-05-18') from dual;
MONTHS_BETWEEN(SYSDATE,DATE'19
------------------------------
453.342685185185
SQL> select months_between(sysdate,date'2001-01-01') from dual;
MONTHS_BETWEEN(SYSDATE,DATE'20
------------------------------
97.8910812425329
9、next_day()给定一个日期值,返回由第二个参数指出的日子第一次出现在的日期值(应返回相应日子的名称字符串)
常用
1. 找出今年的天数
SQL> select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual ;
ADD_MONTHS(TRUNC(SYSDATE,'YEAR
------------------------------
365
SQL> select add_months(trunc(to_date('20081201','yyyymmdd'),'year'), 12) - trunc(to_date('20081201','yyyymmdd'),'year') from dual ;
ADD_MONTHS(TRUNC(TO_DATE('2008
------------------------------
366
闰年的处理方法
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )
如果是28就不是闰年
YYYY:四位表示的年份
YYY,YY,Y:年份的最后三位、两位或一位,缺省为当前世纪
MM:01~12的月份编号
MONTH:九个字符表示的月份,右边用空格填补
MON:三位字符的月份缩写
WW:一年中的星期
D:星期中的第几天
DD:月份中的第几天
DDD:年所中的第几天
DAY:九个字符表示的天的全称,右边用空格补齐
HH,HH12:一天中的第几个小时,12进制表示法
HH24:一天中的第几个小时,取值为00~23
MI:一小时中的分钟
SS:一分钟中的秒
SSSSS:从午夜开始过去的秒数
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
一。基本运算部分
A)to_char函数
1. 年的运算
1.1 Y或YY或YYY 年的最后一位,两位或三位,YYYY 四位年份,IYYY ISO标准的四位年份
SQL> Select to_char(sysdate,'Y'),to_char(sysdate,'YY'),to_char(sysdate,'YYY'),to_char(sysdate,'YYYY'),TO_CHAR(SYSTIMESTAMP, 'IYYY') from dual;
TO_CHAR(SYSDATE,'Y') TO_CHAR(SYSDATE,'YY') TO_CHAR(SYSDATE,'YYY') TO_CHAR(SYSDATE,'YYYY') TO_CHAR(SYSTIMESTAMP,'IYYY')
-------------------- --------------------- ---------------------- ----------------------- ----------------------------
9 09 009 2009 2009
1.2 YEAR
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'YEAR') from dual;
TO_CHAR(SYSTIMESTAMP,'YEAR')
------------------------------------------
TWO THOUSAND NINE
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'YEAR','NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(SYSTIMESTAMP,'YEAR','N
------------------------------------------
TWO THOUSAND NINE
2.Q 季度 1~3月为第一季度,2表示第二季度。
SQL> Select to_char(sysdate,'Q') from dual;
TO_CHAR(SYSDATE,'Q')
--------------------
1
3. 月份数
3.1 MM 月份数
SQL> Select to_char(sysdate,'MM') from dual;
TO_CHAR(SYSDATE,'MM')
---------------------
02
3.2 Month 用9个字符长度表示的月份名
SQL> Select to_char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------
2月
3.3 月份函数汇总:
SQL> Select to_char(sysdate,'Month'),
2 to_char(sysdate,'mon'),
3 to_char(sysdate,'Month'),
4 to_char(sysdate,'mon','NLS_DATE_LANGUAGE = American'), --三位字符的月份缩写全部小写
5 to_char(sysdate,'Mon','NLS_DATE_LANGUAGE = American'), --三位字符的月份缩写首字母大些
6 to_char(sysdate,'MON','NLS_DATE_LANGUAGE = American'), --三位字符的月份缩写全部大些
7 to_char(sysdate,'month','NLS_DATE_LANGUAGE = American'), --九个字符表示的月份,右边用空格填补全部小写
8 to_char(sysdate,'Month','NLS_DATE_LANGUAGE = American'), --九个字符表示的月份,右边用空格填补首字母大些
9 to_char(sysdate,'MONTH','NLS_DATE_LANGUAGE = American') --九个字符表示的月份,右边用空格填补全部大些
10 FROM dual;
TO_CHAR(SYSDATE,'MONTH') TO_CHAR(SYSDATE,'MON') TO_CHAR(SYSDATE,'MONTH') TO_CHAR(SYSDATE,'MON','NLS_DAT TO_CHAR(SYSDATE,'MON','NLS_DAT TO_CHAR(SYSDATE,'MON','NLS_DAT TO_CHAR(SYSDATE,'MONTH','NLS_D TO_CHAR(SYSDATE,'MONTH','NLS_D TO_CHAR(SYSDATE,'MONTH','NLS_D
------------------------ ---------------------- ------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
2月 2月 2月 feb Feb FEB february February FEBRUARY
3.4.RM 月份的罗马表示 (V在罗马数字中表示 5)
SQL> Select to_char(sysdate,'RM') from dual;
TO_CHAR(SYSDATE,'RM')
---------------------
II
4 周的运算
4.1 WW 当年第几周 (2009年2月27日为2009年的第9周)
SQL> Select to_char(sysdate,'WW') from dual;
TO_CHAR(SYSDATE,'WW')
---------------------
09
SQL>
4.2 W 本月第几周 (2009年2月27日为2月第4周)
SQL> Select to_char(sysdate,'W') from dual;
TO_CHAR(SYSDATE,'W')
--------------------
4
6. 天的运算
6.1 DDD 当年第几天 (2009年2月27日为2009年第58天)
SQL> Select to_char(sysdate,'DDD') from dual;
TO_CHAR(SYSDATE,'DDD')
----------------------
058
6.2 DD 当月第几天 (2009年2月27日为2月的第27天)
SQL> Select to_char(sysdate,'DD') from dual;
TO_CHAR(SYSDATE,'DD')
---------------------
27
6.3 D 周内第几天 (2009年2月27日为这一周的第6天,周五)
数据字显示和星期对应关系如下)
1 2 3 4 5 6 7
日 一 二 三 四 五 六
SQL> Select to_char(sysdate,'D') from dual;
TO_CHAR(SYSDATE,'D')
--------------------
6
6.4 DAY 中文(英文)的星期几 ( 2009年2月27日为星期五)
SQL> Select to_char(sysdate,'DAY') from dual;
TO_CHAR(SYSDATE,'DAY')
----------------------
星期五
6.5 DY 中文(英文)的星期几 ( 2009年2月27日为星期五)
SQL> Select to_char(sysdate,'DY') from dual;
TO_CHAR(SYSDATE,'DY')
---------------------
星期五
6.6 日函数运算汇总:
SQL> Select
2 to_char(sysdate,'D'),
3 to_char(sysdate,'DD'),
4 to_char(sysdate,'DDD'),
5 to_char(sysdate,'dy','NLS_DATE_LANGUAGE = American'),
6 to_char(sysdate,'Dy','NLS_DATE_LANGUAGE = American'),
7 to_char(sysdate,'DY','NLS_DATE_LANGUAGE = American'),
8 to_char(sysdate,'day','NLS_DATE_LANGUAGE = American'),
9 to_char(sysdate,'Day','NLS_DATE_LANGUAGE = American'),
10 to_char(sysdate,'DAY','NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(SYSDATE,'D') TO_CHAR(SYSDATE,'DD') TO_CHAR(SYSDATE,'DDD') TO_CHAR(SYSDATE,'DY','NLS_DATE TO_CHAR(SYSDATE,'DY','NLS_DATE TO_CHAR(SYSDATE,'DY','NLS_DATE TO_CHAR(SYSDATE,'DAY','NLS_DAT TO_CHAR(SYSDATE,'DAY','NLS_DAT TO_CHAR(SYSDATE,'DAY','NLS_DAT
-------------------- --------------------- ---------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
6 27 058 fri Fri FRI friday Friday FRIDAY
SQL> Select
2 to_char(sysdate,'D')||chr(10)||
3 to_char(sysdate,'DD')||chr(10)||
4 to_char(sysdate,'DDD')||chr(10)||
5 to_char(sysdate,'dy','NLS_DATE_LANGUAGE = American')||chr(10)||
6 to_char(sysdate,'Dy','NLS_DATE_LANGUAGE = American')||chr(10)||
7 to_char(sysdate,'DY','NLS_DATE_LANGUAGE = American')||chr(10)||
8 to_char(sysdate,'day','NLS_DATE_LANGUAGE = American')||chr(10)||
9 to_char(sysdate,'Day','NLS_DATE_LANGUAGE = American')||chr(10)||
10 to_char(sysdate,'DAY','NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(SYSDATE,'D')||CHR(10)|
--------------------------------------------------
6
27
058
fri
Fri
FRI
friday
Friday
FRIDAY
7 小时的运算
7.1.HH或HH12 12进制小时数 (16:09分为用12小时制计时为4点)
SQL> Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate,'HH') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2 TO_CHAR(SYSDATE,'HH')
------------------------------ ---------------------
2009-02-28 10:58:54 10
7.2.HH24 24小时制
SQL> Select to_char(sysdate+0.5,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+0.5,'HH'),to_char(sysdate+0.5,'HH24') from dual;
TO_CHAR(SYSDATE+0.5,'YYYY-MM-D TO_CHAR(SYSDATE+0.5,'HH') TO_CHAR(SYSDATE+0.5,'HH24')
------------------------------ ------------------------- ---------------------------
2009-02-28 22:59:49 10 22
8 上下午的运算
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'AM','NLS_DATE_LANGUAGE = American'),TO_CHAR(SYSTIMESTAMP, 'AM'),
2 TO_CHAR(SYSTIMESTAMP, 'PM','NLS_DATE_LANGUAGE = American'),TO_CHAR(SYSTIMESTAMP, 'PM'),
3 TO_CHAR(SYSTIMESTAMP+0.5, 'PM','NLS_DATE_LANGUAGE = American'),TO_CHAR(SYSTIMESTAMP+0.5, 'PM') from dual;
TO_CHAR(SYSTIMESTAMP,'AM','NLS TO_CHAR(SYSTIMESTAMP,'AM') TO_CHAR(SYSTIMESTAMP,'PM','NLS TO_CHAR(SYSTIMESTAMP,'PM') TO_CHAR(SYSTIMESTAMP+0.5,'PM', TO_CHAR(SYSTIMESTAMP+0.5,'PM')
------------------------------ -------------------------- ------------------------------ -------------------------- ------------------------------ ------------------------------
AM 上午 AM 上午 PM 下午
9 MI:一小时中的分钟 Minute (0-59).
SQL> Select to_char(sysdate,'mi') from dual;
TO_CHAR(SYSDATE,'MI')
---------------------
54
10 秒的运算
10.1 SS:一分钟中的秒 ,SSSSs:从午夜开始过去的秒数
SQL> Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate,'ss'),to_char(sysdate,'SSSSS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2 TO_CHAR(SYSDATE,'SS') TO_CHAR(SYSDATE,'SSSSS')
------------------------------ --------------------- ------------------------
2009-02-28 11:06:04 04 39964
10.2 这个好像取到6位就行了
SQL> SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||chr(10)||
2 TO_CHAR(SYSTIMESTAMP, 'SS.FF1')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF2')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF3')||chr(10)||
3 TO_CHAR(SYSTIMESTAMP, 'SS.FF4')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF5')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF6')||chr(10)||
4 TO_CHAR(SYSTIMESTAMP, 'SS.FF7')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF8')||chr(10)||TO_CHAR(SYSTIMESTAMP, 'SS.FF9') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
--------------------------------------------------------------------------------
2009-02-28 11:14:21
21.8
21.81
21.810
21.8100
21.81002
21.810022
21.8100220
21.81002200
21.810022000
B)trunc()函数,都是对数据向前取整
1. 计算小时,分,秒,毫秒
SQL> SELECT DAYS,
2 A,
3 TRUNC(A * 24) HOURS,
4 TRUNC(A * 24 * 60 - 60 * TRUNC(A * 24)) MINUTES,
5 TRUNC(A * 24 * 60 * 60 - 60 * TRUNC(A * 24 * 60)) SECONDS,
6 TRUNC(A * 24 * 60 * 60 * 100 - 100 * TRUNC(A * 24 * 60 * 60)) MSECONDS
7 FROM (SELECT TRUNC(SYSDATE) DAYS, SYSDATE - TRUNC(SYSDATE) A FROM DUAL);
DAYS A HOURS MINUTES SECONDS MSECONDS
----------- ---------- ---------- ---------- ---------- ----------
2009-2-28 0.56979166 13 40 30 0
2. 世纪第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'cc') from dual;
NOW TRUNC(SYSDATE,'CC')
------------------- -------------------
2009-02-28 13:51:30 2001-1-1
3. 年的第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,
2 trunc(SYSDATE,'SYYYY'), trunc(SYSDATE,'YYYY'),
3 trunc(SYSDATE,'YEAR'), trunc(SYSDATE,'SYEAR'),
4 trunc(SYSDATE,'YYY'), trunc(SYSDATE,'YY'), trunc(SYSDATE,'Y')
5 from dual;
NOW TRUNC(SYSDATE,'SYYYY') TRUNC(SYSDATE,'YYYY') TRUNC(SYSDATE,'YEAR') TRUNC(SYSDATE,'SYEAR') TRUNC(SYSDATE,'YYY') TRUNC(SYSDATE,'YY') TRUNC(SYSDATE,'Y')
------------------- ---------------------- --------------------- --------------------- ---------------------- -------------------- ------------------- ------------------
2009-02-28 13:52:51 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1
4. 季度第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'q'),trunc(SYSDATE+100,'q')from dual;
NOW TRUNC(SYSDATE,'Q') TRUNC(SYSDATE+100,'Q')
------------------- ------------------ ----------------------
2009-02-28 14:07:03 2009-1-1 2009-4-1
5. 月份的第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'MONTH'),trunc(SYSDATE,'MON'),trunc(SYSDATE,'MM'),trunc(SYSDATE,'RM') from dual;
NOW TRUNC(SYSDATE,'MONTH') TRUNC(SYSDATE,'MON') TRUNC(SYSDATE,'MM') TRUNC(SYSDATE,'RM')
------------------- ---------------------- -------------------- ------------------- -------------------
2009-02-28 14:09:08 2009-2-1 2009-2-1 2009-2-1 2009-2-1
6 周
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'WW'),trunc(SYSDATE,'W')from dual;
NOW TRUNC(SYSDATE,'WW') TRUNC(SYSDATE,'W')
------------------- ------------------- ------------------
2009-02-28 14:13:43 2009-2-26 2009-2-22
6.2 周的第一天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'DAY'),trunc(SYSDATE,'DY'),trunc(SYSDATE,'D') from dual;
NOW TRUNC(SYSDATE,'DAY') TRUNC(SYSDATE,'DY') TRUNC(SYSDATE,'D')
------------------- -------------------- ------------------- ------------------
2009-02-28 14:24:31 2009-2-22 2009-2-22 2009-2-22
9. 天
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'DDD'),trunc(SYSDATE,'DD'),trunc(SYSDATE,'J') from dual;
NOW TRUNC(SYSDATE,'DDD') TRUNC(SYSDATE,'DD') TRUNC(SYSDATE,'J')
------------------- -------------------- ------------------- ------------------
2009-02-28 14:21:41 2009-2-28 2009-2-28 2009-2-28
10 小时
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'HH'),trunc(SYSDATE,'HH12'),trunc(SYSDATE,'HH24') from dual;
NOW TRUNC(SYSDATE,'HH') TRUNC(SYSDATE,'HH12') TRUNC(SYSDATE,'HH24')
------------------- ------------------- --------------------- ---------------------
2009-02-28 14:26:39 2009-2-28 14:00:00 2009-2-28 14:00:00 2009-2-28 14:00:00
11 分
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,trunc(SYSDATE,'MI') from dual;
NOW TRUNC(SYSDATE,'MI')
------------------- -------------------
2009-02-28 14:31:37 2009-2-28 14:31:00
C) round()函数 和trunc函数类似,部分取值向后取整
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'cc') from dual;
NOW ROUND(SYSDATE,'CC')
------------------- -------------------
2009-02-28 14:33:10 2001-1-1
SQL> select round(SYSDATE,'cc') from dual;
ROUND(SYSDATE,'CC')
-------------------
2001-1-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,
2 round(SYSDATE,'SYYYY'), round(SYSDATE,'YYYY'),
3 round(SYSDATE,'YEAR'), round(SYSDATE,'SYEAR'),
4 round(SYSDATE,'YYY'), round(SYSDATE,'YY'), round(SYSDATE,'Y')
5 from dual;
NOW ROUND(SYSDATE,'SYYYY') ROUND(SYSDATE,'YYYY') ROUND(SYSDATE,'YEAR') ROUND(SYSDATE,'SYEAR') ROUND(SYSDATE,'YYY') ROUND(SYSDATE,'YY') ROUND(SYSDATE,'Y')
------------------- ---------------------- --------------------- --------------------- ---------------------- -------------------- ------------------- ------------------
2009-02-28 14:33:11 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1 2009-1-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'Q')from dual;
NOW ROUND(SYSDATE,'Q')
------------------- ------------------
2009-02-28 14:33:11 2009-4-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'q'),round(SYSDATE+100,'q')from dual;
NOW ROUND(SYSDATE,'Q') ROUND(SYSDATE+100,'Q')
------------------- ------------------ ----------------------
2009-02-28 14:33:11 2009-4-1 2009-7-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'MONTH'),round(SYSDATE,'MON'),round(SYSDATE,'MM'),round(SYSDATE,'RM') from dual;
NOW ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'MON') ROUND(SYSDATE,'MM') ROUND(SYSDATE,'RM')
------------------- ---------------------- -------------------- ------------------- -------------------
2009-02-28 14:33:11 2009-3-1 2009-3-1 2009-3-1 2009-3-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'WW'),round(SYSDATE,'W')from dual;
NOW ROUND(SYSDATE,'WW') ROUND(SYSDATE,'W')
------------------- ------------------- ------------------
2009-02-28 14:33:11 2009-2-26 2009-3-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'DDD'),round(SYSDATE,'DD'),round(SYSDATE,'J') from dual;
NOW ROUND(SYSDATE,'DDD') ROUND(SYSDATE,'DD') ROUND(SYSDATE,'J')
------------------- -------------------- ------------------- ------------------
2009-02-28 14:33:11 2009-3-1 2009-3-1 2009-3-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'DAY'),round(SYSDATE,'DY'),round(SYSDATE,'D') from dual;
NOW ROUND(SYSDATE,'DAY') ROUND(SYSDATE,'DY') ROUND(SYSDATE,'D')
------------------- -------------------- ------------------- ------------------
2009-02-28 14:33:11 2009-3-1 2009-3-1 2009-3-1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'HH'),round(SYSDATE,'HH12'),round(SYSDATE,'HH24') from dual;
NOW ROUND(SYSDATE,'HH') ROUND(SYSDATE,'HH12') ROUND(SYSDATE,'HH24')
------------------- ------------------- --------------------- ---------------------
2009-02-28 14:33:11 2009-2-28 15:00:00 2009-2-28 15:00:00 2009-2-28 15:00:00
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,round(SYSDATE,'MI') from dual;
NOW ROUND(SYSDATE,'MI')
------------------- -------------------
2009-02-28 14:33:11 2009-2-28 14:33:00
D)TO_DATE使用详解
时常使用to_date函数来查询特定时间内的数据。
语法:
TO_DATE(x [, format])用于将字符串x转换为时间值,该函数可以指定一个可选的字符串format参数来说明x的格式。
如果没有指定format参数,日期就使用默认的数据库格式(通常是DD-MON-YYYY或DD-MON-YY)。
注意:
数据库参数NLS_DATE_FORMAT指定了默认的日期格式。可以修改NLS_DATE_FORMAT的设置。
2. 指定时间
当然,在时间值中也可以指定时间。如果在时间值中没有指定时间,那么时间值的时间部分默认为12:00:00 A.M.。时间可以使用前面表5-2所示的各种格式。时间格式的一个例子是HH24:MI:SS,其中:
● HH24是一个2位数字,表示24小时格式中的小时数,该值的范围为00~23。
● MI 是一个2位数字,表示分钟,该值的范围为00~59。
● SS 是一个2位数字,表示秒,该值的范围为00~59。
使用HH24:MI:SS格式的一个时间的例子是19:23:36。使用这个时间的一个完整时间值是:
05-FEB-1968 19:32:36
这个时间值的格式为:
DD-MON-YYYY HH24:MI:SS
to_data(date&time,format)
其中第一个参数date&time是我们要查询的具体日期和时间,比如:2006年5月21日。但是这个时间也分
为很多种精度的,比如:
2006年5月21日
2006年5月21日17:24
2006年5月21日17:34分55秒
这个精度要根据实际情况来定。
另外一个参数format决定的日期的表示方法,比如:
2006年5月21日 可以表示为:060521、20060521、2006FEB21等等
而他们对应的format为
060521 YYMMDD
20060521 YYYYMMDD
2006FEB21 YYYYMONDD
示例:
select * from book where intime=to_date('2003/02/17/17/53/55','YYYY/MM/DD/HH24/MI/SS')
上面的语句是查询:2003年8月17日下午5点53分55秒,入库的书本。
其实用下面的写法也是可以的
select * from book where intime=to_date('20030217175355','YYYYMMDDHH24MISS')
select * from book where intime=to_date
('2003-02-17-17-53-55','YYYY-MM-DD-HH24/-I-SS')
中间的连字符"/"、":"或是"-",并不影响表达,只是为了更加容易看清楚。
表示年份有以下几种格式,及这种格式对应的表达方法:
2003年为例
YYYY 2003
YYY 003
YY 03
月份表示格式及表达方法:
二月分为例
MM 08
RM IIX(罗马数字)
MONTH february
MON feb
日期的格式及表达方法:
17号为例
DDD 76 2月的17日在本年度(不是闰年的情况)是多少天
DD 17 在本月中是号(17号)
D 在这一个星期是哪天
星期的格式和表示方法:
星期一为例
DAY monday 全名显示
DY mon 缩写
小时的格式和表示方法:
HH24 18 二十四小时制
分钟的格式和表示方法:
MI 32 该小时32分钟
秒的表示方法
SS 28 该分钟28秒
二。 其他运算函数
1、add_months()用于从一个日期值增加或减少一些月份
SQL> select add_months(sysdate,-12) "Last Year ",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now,add_months(sysdate,12) "Next Year " from dual;
Last Year NOW Next Year
------------------ ------------------- ------------------
2008-2-29 14:41:54 2009-02-28 14:41:54 2010-2-28 14:41:54
2、current_date()返回当前会放时区中的当前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;
SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11月-03
SQL> alter session set time_zone='-11:00'
2 /
会话已更改。
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.59.13.668000 下午 -11:00
SQL>
3、current_timestamp()以timestamp with time zone数据类型返回当前会放时区中的当前日期
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
+08:00 13-11月-03 11.56.28.160000 上午 +08:00
SQL> alter session set time_zone='-11:00'
2 /
会话已更改。
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.58.00.243000 下午 -11:00
4、dbtimezone()返回时区
varchar_value:=dbtimezone
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+08:00
5、extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;
This Month
----------
2
SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;
3 Years Out
-----------
2012
6、last_day()返回包含了日期参数的月份的最后一天的日期
date_value:=last_day(date_value)
SQL> select last_day(sysdate) "Last day of this month" from dual;
Last day of this month
----------------------
2009-2-28 14:54:09
7、localtimestamp()返回会话中的日期和时间
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
----------------------------
28-2月 -09 02.54.55.266868 下午
SQL> select localtimestamp,current_timestamp from dual;
LOCALTIMESTAMP CURRENT_TIMESTAMP
---------------------------- ------------------------------------
28-2月 -09 02.56.39.923571 下午 28-2月 -09 02.56.39.923571 下午 +08:00
8、months_between()判断两个日期之间的月份数量
number_value:=months_between(date_value,date_value)
SQL> select months_between(sysdate,date'1971-05-18') from dual;
MONTHS_BETWEEN(SYSDATE,DATE'19
------------------------------
453.342685185185
SQL> select months_between(sysdate,date'2001-01-01') from dual;
MONTHS_BETWEEN(SYSDATE,DATE'20
------------------------------
97.8910812425329
9、next_day()给定一个日期值,返回由第二个参数指出的日子第一次出现在的日期值(应返回相应日子的名称字符串)
常用
1. 找出今年的天数
SQL> select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual ;
ADD_MONTHS(TRUNC(SYSDATE,'YEAR
------------------------------
365
SQL> select add_months(trunc(to_date('20081201','yyyymmdd'),'year'), 12) - trunc(to_date('20081201','yyyymmdd'),'year') from dual ;
ADD_MONTHS(TRUNC(TO_DATE('2008
------------------------------
366
闰年的处理方法
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )
如果是28就不是闰年