oracle 日期函数

 

1、出输系统时间
select to_char(sysdate,'DD-MM-YYYY HH:MI:SS AM') as 别名 from 表

2、months_between()判断两个日期之间的月份数量
select months_between(date'2008-01-16',date'2007-01-16') how from budget_voucher
select months_between(sysdate,date'2007-01-16') how from budget_voucher

3、add_months()用于从一个日期值增加或减少一些月份
select add_months(sysdate,12) "Next Year" from budget_voucher
运行脚本日期:2008-1-16 13:54:14

4、current_date()返回当前会放时区中的当前日期 运行脚本时间2008-1-17，十点左右
select sessiontimezone,current_date from budget_voucher

alter session set time_zone='-11:00'
select sessiontimezone,current_date from budget_voucher

5、current_timestamp() 同4
select sessiontimezone,current_timestamp from budget_voucher

alter session set time_zone='-11:00'
select sessiontimezone,current_timestamp from budget_voucher

6、dBTimezone()返回时区
select dbtimezone from budget_voucher

7、extract()找出日期或间隔值的字段值
select extract(month from sysdate) "This Month" from budget_voucher

select extract(year from add_months(sysdate,36)) "3 Years Out" from budget_voucher

8、last_day()返回包含了日期参数的月份的最后一天的日期
select last_day(date'2008-01-01') "last date?" from budget_voucher

select last_day(sysdate) "这个月的最后一天的时间" from budget_voucher 运行时间为2008-1-17 10:31:10

9、localtimestamp()返回会话中的日期和时间
SQL> column localtimestamp format a28
　　select localtimestamp from budget_voucher　
　
　　SQL> select localtimestamp,current_timestamp from 表l;
alter session set time_zone='8:00'
　　select localtimestamp,current_timestamp from budget_voucher

　　alter session set time_zone='-11:00'会话已更改。
　　select localtimestamp,current_timestamp from budget_voucher


　select localtimestamp,to_char(sysdate,'DD-MM-YYYY HH:MI:SS AM') "SYSDATE" from budget_voucher


alter session set time_zone='8:00'
select localtimestamp,to_char(sysdate,'DD-MM-YYYY HH:MI:SS AM') "SYSDATE" from budget_voucher

原文：http://megnlingyun.blog.ccidnet.com/blog-htm-itemid-240498-do-showone-type-blog-uid-35878.html