一、 日期函数
名称 | 格式 | 功能 | 实例 | |||||||
add_months | add_months( date1, n ) | >0添加N个月 <0减去N个月 | add_months('01-Aug-03', 3) return '01-Nov-03' add_months('01-Aug-03', -3) return '01-May-03' | |||||||
current_date | current_date | 返回当前会话的当前日期和时区 | select current_date from dual; return 9/10/2005 10:58:24 PM | |||||||
current_timestamp | current_timestamp | 返回TIMESTAMP WITH TIME ZONE | select current_timestamp from dual; return 10-Sep-05 10.58.24.853421 PM -07:00 | |||||||
dbtimezone | dbtimezone | 返回当前会话的时区 | select dbtimezone from dual; return -07:00 | |||||||
from_tz | from_tz( timestamp_value, time_zone_value ) | TIMESTAMPà TIMESTAMP WITH TIME ZONE | select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual; return 11-Sep-05 01.50.42.000000 AM +05:00 | |||||||
last_day | last_day( date ) | 当前日期所在月的最后一天 | last_day(to_date('2003/03/15', 'yyyy/mm/dd')) return Mar 31, 2003 last_day(to_date('2003/02/03', 'yyyy/mm/dd')) return Feb 28, 2003 | |||||||
localtimestamp | localtimestamp | TIMESTAMP | select localtimestamp from dual; return 10-Sep-05 10.58.24 PM | |||||||
months_between | months_between( date1, date2 ) | 返回2个日期间相差的月份 | months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) return -2.41935483870968 months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) return 3.58064516129032 | |||||||
new_time | new_time( date, zone1, zone2 ) | 变换日期的时区 | new_time (to_date ('2003/11/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') return '2003/10/31 10:45:00 PM'. | |||||||
next_day | next_day( date, weekday ) weekday:SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY | 获得当前日期的下一个指定星期的日期。 | next_day('01-Aug-03', 'TUESDAY') return '05-Aug-03' next_day('06-Aug-03', 'WEDNESDAY') return '13-Aug-03' next_day('06-Aug-03', 'SUNDAY') return '10-Aug-03' | |||||||
round | round( date, [ format ] ) | 获得当前日期比较临近的要求的日期 | round(to_date ('22-AUG-03'),'YEAR') return '01-JAN-04' round(to_date ('22-AUG-03'),'Q') return '01-OCT-03' round(to_date ('22-AUG-03'),'MONTH') return '01-SEP-03' round(to_date ('22-AUG-03'),'DDD') return '22-AUG-03' | |||||||
sessiontimezone | sessiontimezone | 当前会话所在的时间时区 | select sessiontimezone from dual; return -07:00 | |||||||
sysdate | sysdate | 本地数据库所在系统的当前日期和时间 |
| |||||||
systimestamp
| systimestamp | 本地数据库所在系统的当前日期和时间包括时区 |
| |||||||
trunc | trunc ( date, [ format ] ) |
| trunc(to_date('22-AUG-03'), 'YEAR') return '01-JAN-03' trunc(to_date('22-AUG-03'), 'Q') return '01-JUL-03' trunc(to_date('22-AUG-03'), 'MONTH') return '01-AUG-03' | |||||||
tz_offset | tz_offset( timezone ) | 获得时区 | tz_offset('US/Michigan') return '-05:00' tz_offset('-08:00') return '-08:00' tz_offset(sessiontimezone) return '-07:00' | |||||||
备注 | ||||||||||
时区类型表示 | ||||||||||
值 | 说明 | 值 | 说明 | |||||||
AST | Atlantic Standard Time | GMT | Greenwich Mean Time | |||||||
ADT | Atlantic Daylight Time | HST | Alaska-Hawaii Standard Time | |||||||
BST | Bering Standard Time | HDT | Alaska-Hawaii Daylight Time | |||||||
BDT | Bering Daylight Time | MST | Mountain Standard Time | |||||||
CST | Central Standard Time | MDT | Mountain Daylight Time | |||||||
CDT | Central Daylight Time | NST | Newfoundland Standard Time | |||||||
EST | Eastern Standard Time | PST | Pacific Standard Time | |||||||
EDT | Eastern Daylight Time | PDT | Pacific Daylight Time | |||||||
YST | Yukon Standard Time | YDT | Yukon Daylight Time | |||||||
常用时区 | ||||||||||
Canada/Atlantic | Pacific/Easter | Canada/Saskatchewan | US/Arizona | Europe/Warsaw | ||||||
Canada/Centra | Pacific/Honolulu | Canada/Yukon | US/Central | US/Pacific | ||||||
Canada/East-Saskatchewan | Pacific/Kwajalein | Europe/Dublin | US/East-Indiana | Europe/Warsaw | ||||||
Canada/Eastern | Pacific/Pago_Pago | Europe/Istanbul | US/Eastern | US/Pacific | ||||||
Canada/Mountain | Pacific/Samoa | Europe/Lisbon | US/Hawaii | Greenwich | ||||||
Canada/Newfoundland | US/Alaska | Europe/London | US/Michigan | US/Pacific-New | ||||||
Canada/Pacific | US/Aleutian | Europe/Moscow | US/Mountain | Pacific/Auckland | ||||||
|
|
| US/Samoa | Pacific/Chatham |