oracle学习笔记（四）--日期函数

一、 日期函数

名称	格式			功能			实例
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