oracle 常用日期函数总结
1. sysdate 查看系统的当前时间select sysdate from dual
SQL> select sysdate from dual;
SYSDATE
---------
14-AUG-13
回显结果受NLS_DATE_FORMAT参数的限制，这里修改下，后面的实验都以YYYY-MM-DD的结果显示
SQL>>
Session> SQL> select sysdate from dual;
SYSDATE
-------------------
2013-08-14 15:27:27
2. ADD_MONTHS(d,n) 当前d日期往后推n个月，下面例子当月是8月加2个月，因此为10月
SQL> select add_months(sysdate,2) from dual;
ADD_MONTHS(SYSDATE,
-------------------
2013-10-14 15:31:20
3. LAST_DATE(D) 当月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------
2013-08-31 15:34:11
4,Months_between(d1,d2) 返回d1和d2之间月的数目，精确到小数位
注意当前日期是8月14号，下面例子表明距10月14还差2个月，以负数形式返回
SQL> select months_between(sysdate,'2013-10-14') from dual;
MONTHS_BETWEEN(SYSDATE,'2013-10-14')
------------------------------------
-2
可以看出该函数以天为单位计算，并且精确到小数位后7位
SQL> select months_between(sysdate,'2013-10-10') from dual;
MONTHS_BETWEEN(SYSDATE,'2013-10-10')
------------------------------------
-1.8498831
2013-10-10 比当前日期超过了
SQL> select months_between('2013-10-10',sysdate) from dual;
MONTHS_BETWEEN('2013-10-10',SYSDATE)
------------------------------------
1.84975545
如果d1，d2相同返回为0
SQL> select months_between(sysdate,sysdate) from dual;
MONTHS_BETWEEN(SYSDATE,SYSDATE)
-------------------------------
0
5. NEXT_DAY(d, day_of_week)
返回由"day_of_week"命名的，在d日期下一个星期的第day_of_week工作日的日期。参数"day_of_week"必须为该星期中的某一天(1-7)。
例如今天是星期三，求下一个工作日的第二天是几号？
SQL> select next_day(sysdate,2) from dual;
NEXT_DAY(SYSDATE,2)
--------------------
2013-08-19 16:05:12
上面求出来的时间是下周一，因为工作日是从星期天到星期六计算
把日期改成本周的17号星期六，可以看到还是一样的结果
SQL> select next_day('2013-08-17',2) from dual;
NEXT_DAY('2013-08-17
--------------------
2013-08-19 00:00:00
6. sessiontimezone 返回当前会话的时区
SQL> select sessiontimezone from dual;
SESSIONTIM
----------
+08:00
7. current_date() 返回当前会话时区中的当前日期
查看当前回话的时区
SQL> col sessiontimezone for a10
SQL> select sessiontimezone,current_date from dual;
SESSIONTIM CURRENT_DATE
---------- --------------------
+08:00 2013-08-14 17:13:13
将session时区修改为西五区，可以看出时间和和东八区相差了13个小时
SQL>>
Session> SQL> select sessiontimezone,current_date from dual;
SESSIONTIM CURRENT_DATE
---------- --------------------
-05:00 2013-08-14 04:16:39
8. current_timestamp 显示了当前会话的时区和时间
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------
14-AUG-13 04.26.41.136929 AM -05:00
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
----------------------------------------------
14-AUG-13 05.27.27.262019 PM +08:00
9.localtimestamp 返回会话中的日期和时间
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------
15-AUG-13 09.48.37.955623 AM
常用的一些日期转换格式
yy显示年份后两位数字
SQL> select to_char(sysdate,'yy') from dual;
TO
--
13
Q显示日期属于第几个季度
SQL> select to_char(sysdate,'Q') from dual;
T
-
3
MM显示月份
SQL> select to_char(sysdate,'MM') from dual;
TO
--
08
RM 月份的罗马表示
SQL> select to_char(sysdate,'RM') from dual;
TO_C
----
VIII
Month 用9个字符长度表示的月份名
注意如果返回值的大小写是根据Month的大小写来指定的
SQL> select to_char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------------------
August
确认字符长度是否为9，表明后面有3个空格
SQL> select to_char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------------------
August
我们用trim()处理下，看是否真的有空格
SQL> select length(trim(to_char(sysdate,'Month'))) from dual;
LENGTH(TRIM(TO_CHAR(SYSDATE,'MONTH')))
--------------------------------------
6
WW 返回日期属于当年的第几周
SQL> select to_char(sysdate,'WW') from dual;
TO
--
33
W 本月第几周
SQL> select to_char(sysdate,'W') from dual;
T
-
3
DDD当年第几天
SQL> select to_char(sysdate,'ddd') from dual;
TO_
---
227
DD当月第几天，每天都在使用的
D 本周第几天
SQL> select to_char(sysdate,'d') from dual;
T
-
5
DAY 返回周内第几天，包含9个字符和Month应用
SQL> select to_char(sysdate,'day') from dual;
TO_CHAR(SYSDATE,'DAY')
------------------------------------
thursday
DY 周内第几天的缩写
SQL> select to_char(sysdate,'Dy') from dual;
TO_CHAR(SYSD
------------
Thu
HH24 24小时制
HH12 12小时制
MI分钟
SS秒
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2013-08-15 11:09:19
下面是一些用法例子:
1. 求上个月的最后一天
分析: 先用add_months求出上个月的今天，然后用last_day求出上月最后一天
今天日期
SQL> select sysdate from dual;
SYSDATE
----------
2013-08-15
上月的今天
SQL> select add_months(sysdate,-1) from dual;
ADD_MONTHS
----------
2013-07-15
上月的最后一天
SQL> select last_day(add_months(sysdate,-1)) from dual;
LAST_DAY(A
----------
2013-07-31
2. 求出上月的第一天
分析:我们知道没有这样的函数，但是我们求出前2个月的的最后一天，然后在加上一天是不是就是上个月的第一天呢？
前两个月的最后一天
SQL> select last_day(add_months(sysdate,-2)) from dual;
LAST_DAY(A
----------
2013-06-30
上月第一天
SQL> select last_day(add_months(sysdate,-2)) + 1 from dual;
LAST_DAY(A
----------
2013-07-01
3. 找到某月中所有周五的具体日期
分析：这个好像有点难噢，我们要是能够构造一张包含当月的日历表，然后从这张表里面找到等于周五的就可以了，前面我们已经知道了如何求周的第几天
构造临时表
先求当月的月份的一天，这里需要用到日期运算，用数字运算函数trunc求，用当前日期截去掉月份
SQL> select trunc(sysdate,'MM') from dual;
TRUNC(SYSD
----------
2013-08-01
这里是用的空转换函数dual，如果只要查询一张大于31行的表，就显示了31个同样的值,这里限制条件只要大于31行就可以了。
SQL> select trunc(sysdate,'MM') from user_objects where rownum < 32；
TRUNC(SYSD
----------
2013-08-01
2013-08-01
2013-08-01
2013-08-01
........
31 rows selected.
然后可以利用rownum生成一个日期序列，用trunc(sysdate,'MM') 加上行号然后减去1是不是就出来了呢？
SQL> select trunc(sysdate,'MM')+rownum-1 from user_objects where rownum < 32;
TRUNC(SYSD
----------
2013-08-01
2013-08-02
2013-08-03
2013-08-04
...
2013-08-27
2013-08-28
2013-08-29
2013-08-30
2013-08-31
31 rows selected.
然后用这个临时表作为子查询，用to_char(mon_date,'DY')='fri'做查询条件，如果用'DAY'我们知道返回时9个字符，需要用trim函数做出来
SQL> select t.mon_date from (select trunc(sysdate,'MM')+rownum-1 mon_date from
ser_objects where rownum < 32) t where to_char(t.mon_date,'dy')='fri';
MON_DATE
----------
2013-08-02
2013-08-09
2013-08-16
2013-08-23
2013-08-30
但是这里有个问题，假设一个月只有30天或者28天怎么办呢？我们只需要过滤下月份就可以了
SQL> select t.mon_date from (select trunc(sysdate,'MM')+rownum-1 mon_date from
user_objects where rownum < 65) t where to_char(t.mon_date,'dy')='fri' and to_char(t.mon_date,'MM')=to_char(sysdate,'MM');
MON_DATE
----------
2013-08-02
2013-08-09
2013-08-16
2013-08-23
2013-08-30
如果题目换一下某个月份的，我们只需要把sysdate改成to_date('yyyy-mm-01','yyy-mm-dd')的形式就可以了,例如求2月份的
select t.mon_date from (select trunc(to_date('2013-02-01','yyyy-mm-dd'),'MM')+rownum-1 mon_date from
user_objects where rownum < 65) t where to_char(t.mon_date,'dy')='fri' and to_char(t.mon_date,'MM')=to_char(to_date('2013-02-01','yyyy-mm-dd'),'MM')；
时间的运算
减一天
SQL> select sysdate-1 from dual;
SYSDATE-1
----------
2013-08-14
SQL> select to_date('2013-02-01','yyyy-mm-dd') -1 from dual;
TO_DATE('2
----------
2013-01-31
减一天一小时
SQL> select to_char(sysdate-1-1/24,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE-1-1
-------------------
2013-08-14 13:34:16
减1天1小时1分
SQL> select to_char(sysdate-1-1/24-1/(24*60),'yyyy-mm-dd hh24:mi:ss') from dua
TO_CHAR(SYSDATE-1-1
-------------------
2013-08-14 13:37:28
减1天1小时1分1秒
SQL> select to_char(sysdate-1-1/24-1/(24*60)-1/(24*60*60),'yyyy-mm-dd hh24:mi:ss
') from dual;
TO_CHAR(SYSDATE-1-1
-------------------
2013-08-14 13:37:41
页:
[1]