Oracle9i 中与时间有关的学习小结 一、Oracle9i 中的时区设置和修改 1、时区的设置 当数据库创建时通过 create database ... set time_zone='AMERICA/NEW_YORK' .. ; 或者通过alter database set time_zone='AMERICA/NEW_YORK'; 设置以后, DBTIMEZONE 就可以返回当前数据库的时区设置信息.如果在创建数据库 时没有指定time_zone 的设置,那么就使用和当前操作系统一致的时区. 有效的时区名称和信息,可以通过视图v$timezone_names 来查看.TZNAME 字段对应 时区的名称. 2、时区的调整 以一个实际案例来说明。 SQL> select dbtimezone from dual; DBTIME ------ 0:00 当时，我们查时间： SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; 2005-01-15 03:15:33 其时，操作时间是2005-01-15 11:15:33，相差是8 个小时。数据库时区和操作系统不 是一个时区。该怎么解决呢？摸着石头过河吧！ 通过查询相关文档，得知可以使用alter database set time_zone 语句修改。 于是，我就以sys 用户登陆数据库，然后发出命令： SQL> alter database set time_zone = '+8:00'; alter database set time_zone = '+8:00' * 第1 行出现错误: ORA-02231: 缺少或无效的ALTER DATABASE 选项 注：问题是数据库中有一些列的数据类型是：TIMESTAMP WITH LOCAL TIME ZONE 只要 把这些列删除了就可以了。metalink 上的230099.1 提供了一个脚本，查找哪些列的数据类 型是TIMESTAMP WITH LOCAL TIME ZONE 类型。执行该脚本： SQL> select u.name || '.' || o.name || '.' || c.name TSLTZcolumn from sys.obj$ o, sys.col$ c, sys.user$ u where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#; TSLTZCOLUMN ------------------------------------------------------------------------ OE.ORDERS.ORDER_DATE 发现oe 用户下orders 表中的字段order_date TIMESTAMP WITH LOCAL TIME ZONE 类 型的： SQL> desc oe.orders 名称是否为空? 类型 ----------------------------------------- -------- ---------------------------- ORDER_ID NOT NULL NUMBER(12) ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE ORDER_MODE VARCHAR2(8) CUSTOMER_ID NOT NULL NUMBER(6) ORDER_STATUS NUMBER(2) ORDER_TOTAL NUMBER(8,2) SALES_REP_ID NUMBER(6) PROMOTION_ID NUMBER(6) 该表是一个无关紧要的表，于是删除该中order_date 字段。 SQL> alter table oe.orders drop column order_date; 表已更改。 然后尝试更改数据库时区： SQL> alter database set time_zone='+8:00'; 数据库已更改。 接着，关闭并重新启动数据库 查询时区信息 SQL> select dbtimezone from dual; DBTIME ------ +08:00 至此，我们已经更改数据库时区成功！ 二、Oracle9i 中的缺省日期格式DD-MON-RR 在ORACLE9I 之前, 日期格式的数据类型默认格式为“DD-MON-YY”，而在ORACLE9I 中变为“DD-MON-RR”。那么这个“RR”究竟代表什么意思呢？ 查阅了相关资料发现，原来Oracle 为了解决千年问题， 而引入了RR 日期型格式。 1、如果当前年份的最后两位数（即不包括世纪）为0—49，并且指定的年份的最后两 位数也为0—49，则返回的日期在本世纪。 例如：当前年份为2005 年，指明的日期是01-OCT-08，则RR 日期格式返回的日期 为：2008 年10 月1 日。而YY 日期格式返回的日期也为：2008 年10 月1 日 2、如果当前年份的最后两位数（即不包括世纪）为0—49，并且指定的年份的最后 两位数为50—99，则返回的日期在上一世纪。 例如：当前年份为2005 年，指明的日期是01-OCT-98，RR 日期格式返回的日期为： 1998 年10 月1 日。而YY 日期格式返回的日期为：2098 年10 月1 日。这也许就是 我们所说的两千年问题。 3、如果当前年份的最后两位数（即不包括世纪）为50—99，并且指定的年份的最后 两位数为0—49，则返回的日期在下一世纪。 例如：当前年份为1999 年，指明的日期是01-OCT-08，RR 日期格式返回的日期为： 2008 年10 月1 日。而YY 日期格式返回的日期为：1908 年10 月1 日。 4、如果当前年份的最后两位数（即不包括世纪）为50—99，并且指定的年份的最后 两位数也为50—99，则返回的日期在本世纪。 例如：当前年份为1999 年，指明的日期是01-OCT-98，RR 日期格式返回的日期为： 1998 年10 月1 日。而YY 日期格式返回的日期也为：1998 年10 月1 日。 Examples To find employees hired prior to 1990, use the RR format, which produces the same results whether the command is run in 1999 or now。 三、Oracle9i 中的常用date 函数 SQL>select last_name, to_char(hire_date, 'DD-Mon-YYYY') from employees where hire_date < to_date('01-Jan-90', 'DD-Mon-RR'); ● Oracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds. ● 日期型字段的插入和更新可以数据型或字符并带to_date 函数说明即可。 ● 缺省的日期格式有NLS_DATE_FORMAT 参数控制，它的缺省格式为DD-MON-RR。 Allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year. Allows you to store 20th century dates in the 21st century in the same way. ● 缺省的时间是夜里00:00:00 (即0 点0 分0 秒)。 ● sysdate 返回的是服务器的时间。 ● 日期格式的显示可以设置。 ● 日期型可以运算。 ● 世纪用cc 表示；年用yyyy 表示，月用mm 表示，日用dd 表示，小时用hh24 表 示，分用mi 表示，秒用ss 表示。 Oracle 中常用的日期操作函数如下： 函数名称功能 months_between 返回两个日期之间的月份数 add_months 返回给定日期增加/减少指定月份后得到的日期 next_day 返回指定日期后的星期对应的日期 last_day 返回当前日期对应的本月中的最后一天的日期 round Round Date trunc Trunc Date 下面分别示例说明 1） months_between(date1,date2) returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2. Examples 2） add_months(date,n) returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d. Examples SQL> select sysdate from dual; 20050423231955 SQL> select add_months(sysdate,1) from dual; 20050523232004 SQL> select add_months(sysdate,-1) from dual; 20050323232016 SQL> 给出date2-date1 的月份 SQL> select months_between('19-12 月-1999','19-3 月-1999') mon_between from dual; MON_BETWEEN ----------- 9 SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.2 0','yyyy.mm.dd')) mon_betw from dual; MON_BETW --------- -60 SQL> 3） next_day(date, char) returns the date of the first weekday named by char that is later than the date date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date. Examples 4）last_day(date) returns the date of the last day of the month that contains date SQL> select sysdate from dual; 20050423231955 SQL> select next_day(sysdate,'星期一') from dual; 20050425230910 SQL> select next_day(sysdate,'星期六') from dual; 20050430231000 SQL> alter session set nls_language=american; Session altered. SQL> select sysdate from dual; 20050423232729 SQL> select next_day(to_date('02-02-2001','dd-mm-yyyy'),'tuesday') from dual; 20010206000000 SQL> Examples
5） round(date) returns date rounded to the unit specified by the format model fmt. If you omit fmt, then date is rounded to the nearest day. Examples SQL> select sysdate from dual; 20050423233317 SQL> select round(sysdate,'year') from dual; 20050101000000 SQL> select round(to_date('20050612123445','yyyymmddhh24miss'),'year') from dual; 20050101000000 SQL> select round(to_date('20050712123445','yyyymmddhh24miss'),'year') from dual; 20060101000000 SQL> SQL> select 2 to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') 3 from dual; TO_CHAR(SY TO_CHAR((S ---------- ---------- 2004.05.09 2004.05.10 SQL> select last_day(sysdate) from dual; LAST_DAY(S ---------- 31-5 月-04 SQL> 6） trunc(date) returns date with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, then date is truncated to the nearest day. Examples 四、Oracle9i 中的常用datetime 函数 Oracle9i 中常用的Datetime 函数如下： tz_offset current_date current_timestamp localtimestamp dbtimezone sessiontimezone extract from_tz to_timestamp to_timestamp_tz to_yminterval SQL>SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL; New Year --------- 01-JAN-92 SQL> 下面分别加以介绍： 1） tz_offset returns the time zone offset corresponding to the value entered based on the date the statement is executed. You can enter a valid time zone name, a time zone offset from UTC (which simply returns itself), or the keyword SESSIONTIMEZONE or DBTIMEZONE. For a listing of valid values, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view. ◆ Display the time zone offset for the time zone 'US/Eastern' ◆ Display the time zone offset for the time zone 'Canada/Yukon' ◆ Display the time zone offset for the time zone 'Europe/London' 2） current_date returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE ◆ Display the current date and time in the session’s time zone . SQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL; -04:00 SQL> SQL> SELECT TZ_OFFSET('Canada/Yukon') FROM DUAL; -07:00 SQL> SQL> SELECT TZ_OFFSET('Europe/London') FROM DUAL; +01:00 SQL> 注： CURRENT_DATE is sensitive to the session time zone. The return value is a date in the Gregorian calendar. 3） current_timestamp returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The time zone displacement reflects the current local time of the SQL session. If you omit precision, then the default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value ◆ Display the current date and fractional time in the session's time zone. SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; 会话已更改。 SQL> ALTER SESSION SET TIME_ZONE = '-5:0'; 会话已更改。 SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; -05:00 24-4 月-2005 19:55:19 SQL> ALTER SESSION SET TIME_ZONE = '-8:0'; 会话已更改。 SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; -08:00 24-4 月-2005 16:57:42 SQL> SQL> ALTER SESSION SET TIME_ZONE = '-5:0'; 会话已更改。 SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; -05:00 24-4 月-05 08.06.12.076000 下午-05:00 SQL> ALTER SESSION SET TIME_ZONE = '-8:0'; 会话已更改。 SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; -08:00 24-4 月-05 05.06.21.159000 下午-08:00 SQL> 注：CURRENT_TIMESTAMP is sensitive to the session time zone. The return value is of the TIMESTAMP WITH TIME ZONE datatype. 4） localtimestamp returns the current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value. ◆ Display the current date and time in the session time zone in a value of TIMESTAMP data type 注： LOCALTIMESTAMP returns a TIMESTAMP value, whereas CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value. 5） dbtimezone returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement. ◆ Display the value of the database time zone. SQL> ALTER SESSION SET TIME_ZONE = '-5:0'; 会话已更改。 SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP from dual; 24-4 月-05 08.10.27.303000 下午-05:00 24-4 月-05 08.10.27.303000 下午 SQL> ALTER SESSION SET TIME_ZONE = '-8:0'; 会话已更改。 SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; 24-4 月-05 05.10.41.944000 下午-08:00 24-4 月-05 05.10.41.944000 下午 SQL> 6） sessiontimezone returns the value of the current session's time zone. The return type is a time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement. ◆ Display the value of the session's time zone. 7） extract returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view. ◆ Display the YEAR component from the SYSDATE. SQL> SELECT DBTIMEZONE FROM DUAL; -07:00 SQL> SQL> SELECT SESSIONTIMEZONE FROM DUAL; -08:00 SQL> SQL> SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL; 2005 SQL> ◆ Display the MONTH component from the HIRE_DATE for those employees whose MANAGER_ID is 100. 8） from_tz converts a timestamp value at a time zone to a TIMESTAMP WITH TIME ZONE value. time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format. ◆ Display the TIMESTAMP value '2000-03-28 08:00:00' as a TIMESTAMP WITH TIME ZONE value. ◆ Display the TIMESTAMP value '2000-03-28 08:00:00' as a TIMESTAMP WITH TIME ZONE value for the time zone region 'Australia/North' 9） to_timestamp SQL> SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) FROM employees WHERE manager_id = 100 SQL> SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00','3:00') 2 from dual; 28-3 月-00 08.00.00.000000000 上午+03:00 SQL> SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', 'Australia/North') 2 from dual; 28-3 月-00 08.00.00.000000000 上午AUSTRALIA/NORTH SQL> converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype. ◆ Display the character string '2000-12-01 11:00:00'as a TIMESTAMP value. 10）to_timestamp_tz converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype. ◆ Display the character string '1999-12-01 11:00:00 -8:00'as a TIMESTAMP WITH TIME ZONE value. 11）to_yminterval converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted. ◆ Display a date that is one year two months after the hire date for the employees working in the department with the DEPARTMENT_ID 20 SQL> SELECT TO_TIMESTAMP ('2000-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') 2 from dual; 01-12 月-00 11.00.00.000000000 上午 SQL> SQL>select to_timestamp_tz('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL 01-12 月-99 11.00.00.000000000 上午-08:00 SQL> SQL>SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM EMPLOYEES WHERE department_id = 20; ------------------------------------------------------------------------------- MSN：[email protected] My blog：http://blog.itpub.net/toms_zhang 2005 年4 月25 日 -------------------------------------------------------------------------------
一、Oracle9i 中的时区设置和修改
1、时区的设置
当数据库创建时通过
create database
...
set time_zone='AMERICA/NEW_YORK'
..
;
或者通过alter database set time_zone='AMERICA/NEW_YORK';
设置以后, DBTIMEZONE 就可以返回当前数据库的时区设置信息.如果在创建数据库
时没有指定time_zone 的设置,那么就使用和当前操作系统一致的时区.
有效的时区名称和信息,可以通过视图v$timezone_names 来查看.TZNAME 字段对应
时区的名称.
2、时区的调整
以一个实际案例来说明。
SQL> select dbtimezone from dual;
DBTIME
------
0:00
当时，我们查时间：
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
2005-01-15 03:15:33
其时，操作时间是2005-01-15 11:15:33，相差是8 个小时。数据库时区和操作系统不
是一个时区。该怎么解决呢？摸着石头过河吧！
通过查询相关文档，得知可以使用alter database set time_zone 语句修改。
于是，我就以sys 用户登陆数据库，然后发出命令：
SQL> alter database set time_zone = '+8:00';
alter database set time_zone = '+8:00'
*
第1 行出现错误:
ORA-02231: 缺少或无效的ALTER DATABASE 选项
注：问题是数据库中有一些列的数据类型是：TIMESTAMP WITH LOCAL TIME ZONE 只要
把这些列删除了就可以了。metalink 上的230099.1 提供了一个脚本，查找哪些列的数据类
型是TIMESTAMP WITH LOCAL TIME ZONE 类型。执行该脚本：
SQL> select u.name || '.' || o.name || '.' || c.name TSLTZcolumn from sys.obj$
o, sys.col$ c, sys.user$ u where c.type# = 231 and o.obj# = c.obj# and u.user# =
o.owner#;
TSLTZCOLUMN
------------------------------------------------------------------------
OE.ORDERS.ORDER_DATE
发现oe 用户下orders 表中的字段order_date TIMESTAMP WITH LOCAL TIME ZONE 类
型的：
SQL> desc oe.orders
名称是否为空? 类型
----------------------------------------- --------
----------------------------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME
ZONE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NOT NULL NUMBER(6)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
SALES_REP_ID NUMBER(6)
PROMOTION_ID NUMBER(6)
该表是一个无关紧要的表，于是删除该中order_date 字段。
SQL> alter table oe.orders drop column order_date;
表已更改。
然后尝试更改数据库时区：
SQL> alter database set time_zone='+8:00';
数据库已更改。
接着，关闭并重新启动数据库
查询时区信息
SQL> select dbtimezone from dual;
DBTIME
------
+08:00
至此，我们已经更改数据库时区成功！
二、Oracle9i 中的缺省日期格式DD-MON-RR
在ORACLE9I 之前, 日期格式的数据类型默认格式为“DD-MON-YY”，而在ORACLE9I
中变为“DD-MON-RR”。那么这个“RR”究竟代表什么意思呢？
查阅了相关资料发现，原来Oracle 为了解决千年问题， 而引入了RR 日期型格式。
1、如果当前年份的最后两位数（即不包括世纪）为0—49，并且指定的年份的最后两
位数也为0—49，则返回的日期在本世纪。
例如：当前年份为2005 年，指明的日期是01-OCT-08，则RR 日期格式返回的日期
为：2008 年10 月1 日。而YY 日期格式返回的日期也为：2008 年10 月1 日
2、如果当前年份的最后两位数（即不包括世纪）为0—49，并且指定的年份的最后
两位数为50—99，则返回的日期在上一世纪。
例如：当前年份为2005 年，指明的日期是01-OCT-98，RR 日期格式返回的日期为：
1998 年10 月1 日。而YY 日期格式返回的日期为：2098 年10 月1 日。这也许就是
我们所说的两千年问题。
3、如果当前年份的最后两位数（即不包括世纪）为50—99，并且指定的年份的最后
两位数为0—49，则返回的日期在下一世纪。
例如：当前年份为1999 年，指明的日期是01-OCT-08，RR 日期格式返回的日期为：
2008 年10 月1 日。而YY 日期格式返回的日期为：1908 年10 月1 日。
4、如果当前年份的最后两位数（即不包括世纪）为50—99，并且指定的年份的最后
两位数也为50—99，则返回的日期在本世纪。
例如：当前年份为1999 年，指明的日期是01-OCT-98，RR 日期格式返回的日期为：
1998 年10 月1 日。而YY 日期格式返回的日期也为：1998 年10 月1 日。
Examples
To find employees hired prior to 1990, use the RR format, which produces the
same results whether the command is run in 1999 or now。
三、Oracle9i 中的常用date 函数
SQL>select last_name, to_char(hire_date, 'DD-Mon-YYYY')
from employees
where hire_date < to_date('01-Jan-90', 'DD-Mon-RR');
● Oracle database stores dates in an internal numeric format: century, year,
month, day, hours, minutes, seconds.
● 日期型字段的插入和更新可以数据型或字符并带to_date 函数说明即可。
● 缺省的日期格式有NLS_DATE_FORMAT 参数控制，它的缺省格式为DD-MON-RR。
Allows you to store 21st century dates in the 20th century by specifying
only the last two digits of the year.
Allows you to store 20th century dates in the 21st century in the same way.
● 缺省的时间是夜里00:00:00 (即0 点0 分0 秒)。
● sysdate 返回的是服务器的时间。
● 日期格式的显示可以设置。
● 日期型可以运算。
● 世纪用cc 表示；年用yyyy 表示，月用mm 表示，日用dd 表示，小时用hh24 表
示，分用mi 表示，秒用ss 表示。
Oracle 中常用的日期操作函数如下：
函数名称功能
months_between 返回两个日期之间的月份数
add_months 返回给定日期增加/减少指定月份后得到的日期
next_day 返回指定日期后的星期对应的日期
last_day 返回当前日期对应的本月中的最后一天的日期
round Round Date
trunc Trunc Date
下面分别示例说明
1） months_between(date1,date2)
returns number of months between dates date1 and date2. If date1 is later
than date2, then the result is positive. If date1 is earlier than date2, then the
result is negative. If date1 and date2 are either the same days of the month or both
last days of months, then the result is always an integer. Otherwise Oracle
calculates the fractional portion of the result based on a 31-day month and considers
the difference in time components date1 and date2.
Examples
2） add_months(date,n)
returns the date d plus n months. The argument n can be any integer. If d
is the last day of the month or if the resulting month has fewer days than the
day component of d, then the result is the last day of the resulting month.
Otherwise, the result has the same day component as d.
Examples
SQL> select sysdate from dual;
20050423231955
SQL> select add_months(sysdate,1) from dual;
20050523232004
SQL> select add_months(sysdate,-1) from dual;
20050323232016
SQL>
给出date2-date1 的月份
SQL> select months_between('19-12 月-1999','19-3 月-1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.2
0','yyyy.mm.dd')) mon_betw from dual;
MON_BETW
---------
-60
SQL>
3） next_day(date, char)
returns the date of the first weekday named by char that is later than the
date date. The argument char must be a day of the week in the date language
of your session, either the full name or the abbreviation. The minimum number
of letters required is the number of letters in the abbreviated version. Any
characters immediately following the valid abbreviation are ignored. The
return value has the same hours, minutes, and seconds component as the argument
date.
Examples
4）last_day(date)
returns the date of the last day of the month that contains date
SQL> select sysdate from dual;
20050423231955
SQL> select next_day(sysdate,'星期一') from dual;
20050425230910
SQL> select next_day(sysdate,'星期六') from dual;
20050430231000
SQL> alter session set nls_language=american;
Session altered.
SQL> select sysdate from dual;
20050423232729
SQL> select next_day(to_date('02-02-2001','dd-mm-yyyy'),'tuesday')
from dual;
20010206000000
SQL>
Examples
returns date rounded to the unit specified by the format model fmt. If you
omit fmt, then date is rounded to the nearest day.
Examples
SQL> select sysdate from dual;
20050423233317
SQL> select round(sysdate,'year') from dual;
20050101000000
SQL> select round(to_date('20050612123445','yyyymmddhh24miss'),'year') from
dual;
20050101000000
SQL> select round(to_date('20050712123445','yyyymmddhh24miss'),'year') from
dual;
20060101000000
SQL>
SQL> select
2 to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd')
3 from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5 月-04
SQL>
6） trunc(date)
returns date with the time portion of the day truncated to the unit specified
by the format model fmt. If you omit fmt, then date is truncated to the nearest
day.
Examples
四、Oracle9i 中的常用datetime 函数
Oracle9i 中常用的Datetime 函数如下：
tz_offset
current_date
current_timestamp
localtimestamp
dbtimezone
sessiontimezone
extract
from_tz
to_timestamp
to_timestamp_tz
to_yminterval
SQL>SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;
New Year
---------
01-JAN-92
SQL>
下面分别加以介绍：
1） tz_offset
returns the time zone offset corresponding to the value entered based on
the date the statement is executed. You can enter a valid time zone name, a
time zone offset from UTC (which simply returns itself), or the keyword
SESSIONTIMEZONE or DBTIMEZONE. For a listing of valid values, query the TZNAME
column of the V$TIMEZONE_NAMES dynamic performance view.
◆ Display the time zone offset for the time zone 'US/Eastern'
◆ Display the time zone offset for the time zone 'Canada/Yukon'
◆ Display the time zone offset for the time zone 'Europe/London'
2） current_date
returns the current date in the session time zone, in a value in the
Gregorian calendar of datatype DATE
◆ Display the current date and time in the session’s time zone .
SQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
-04:00
SQL>
SQL> SELECT TZ_OFFSET('Canada/Yukon') FROM DUAL;
-07:00
SQL>
SQL> SELECT TZ_OFFSET('Europe/London') FROM DUAL;
+01:00
SQL>
注： CURRENT_DATE is sensitive to the session time zone.
The return value is a date in the Gregorian calendar.
3） current_timestamp
returns the current date and time in the session time zone, in a value of
datatype TIMESTAMP WITH TIME ZONE. The time zone displacement reflects the current
local time of the SQL session. If you omit precision, then the default is 6. The
difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP
returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP
value
◆ Display the current date and fractional time in the session's time zone.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
会话已更改。
SQL> ALTER SESSION SET TIME_ZONE = '-5:0';
会话已更改。
SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
-05:00 24-4 月-2005 19:55:19
SQL> ALTER SESSION SET TIME_ZONE = '-8:0';
会话已更改。
SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
-08:00 24-4 月-2005 16:57:42
SQL>
SQL> ALTER SESSION SET TIME_ZONE = '-5:0';
会话已更改。
SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
-05:00
24-4 月-05 08.06.12.076000 下午-05:00
SQL> ALTER SESSION SET TIME_ZONE = '-8:0';
会话已更改。
SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
-08:00
24-4 月-05 05.06.21.159000 下午-08:00
SQL>
注：CURRENT_TIMESTAMP is sensitive to the session time zone.
The return value is of the TIMESTAMP WITH TIME ZONE datatype.
4） localtimestamp
returns the current date and time in the session time zone in a value of
datatype TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is
that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a
TIMESTAMP WITH TIME ZONE value.
◆ Display the current date and time in the session time zone in a value
of TIMESTAMP data type
注： LOCALTIMESTAMP returns a TIMESTAMP value, whereas CURRENT_TIMESTAMP
returns a TIMESTAMP WITH TIME ZONE value.
5） dbtimezone
returns the value of the database time zone. The return type is a time zone
offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name,
depending on how the user specified the database time zone value in the most recent
CREATE DATABASE or ALTER DATABASE statement.
◆ Display the value of the database time zone.
SQL> ALTER SESSION SET TIME_ZONE = '-5:0';
会话已更改。
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP from dual;
24-4 月-05 08.10.27.303000 下午-05:00
24-4 月-05 08.10.27.303000 下午
SQL> ALTER SESSION SET TIME_ZONE = '-8:0';
会话已更改。
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
24-4 月-05 05.10.41.944000 下午-08:00
24-4 月-05 05.10.41.944000 下午
SQL>
6） sessiontimezone
returns the value of the current session's time zone. The return type is
a time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone
region name, depending on how the user specified the session time zone value in the
most recent ALTER SESSION statement.
◆ Display the value of the session's time zone.
7） extract
returns the value of a specified datetime field from a datetime or interval
value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR
(abbreviation), the value returned is a string containing the appropriate time zone
name or abbreviation. When you extract any of the other values, the value returned
is in the Gregorian calendar. When extracting from a datetime with a time zone value,
the value returned is in UTC. For a listing of time zone names and their corresponding
abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
◆ Display the YEAR component from the SYSDATE.
SQL> SELECT DBTIMEZONE FROM DUAL;
-07:00
SQL>
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
-08:00
SQL>
SQL> SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
2005
SQL>
◆ Display the MONTH component from the HIRE_DATE for those employees whose
MANAGER_ID is 100.
8） from_tz
converts a timestamp value at a time zone to a TIMESTAMP WITH TIME ZONE
value. time_zone_value is a character string in the format 'TZH:TZM' or a character
expression that returns a string in TZR with optional TZD format.
◆ Display the TIMESTAMP value '2000-03-28 08:00:00' as a TIMESTAMP WITH TIME
ZONE value.
◆ Display the TIMESTAMP value '2000-03-28 08:00:00' as a TIMESTAMP WITH TIME
ZONE value for the time zone region 'Australia/North'
9） to_timestamp
SQL> SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE)
FROM employees
WHERE manager_id = 100
SQL>
SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00','3:00')
2 from dual;
28-3 月-00 08.00.00.000000000 上午+03:00
SQL>
SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', 'Australia/North')
2 from dual;
28-3 月-00 08.00.00.000000000 上午AUSTRALIA/NORTH
SQL>
converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value
of TIMESTAMP datatype.
◆ Display the character string '2000-12-01 11:00:00'as a TIMESTAMP value.
10）to_timestamp_tz
converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value
of TIMESTAMP datatype.
◆ Display the character string '1999-12-01 11:00:00 -8:00'as a TIMESTAMP
WITH TIME ZONE value.
11）to_yminterval
converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype
to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted.
◆ Display a date that is one year two months after the hire date for the
employees working in the department with the DEPARTMENT_ID 20
SQL> SELECT TO_TIMESTAMP ('2000-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS')
2 from dual;
01-12 月-00 11.00.00.000000000 上午
SQL>
SQL>select to_timestamp_tz('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS
TZH:TZM') FROM DUAL
01-12 月-99 11.00.00.000000000 上午-08:00
SQL>
SQL>SELECT hire_date,
hire_date + TO_YMINTERVAL('01-02') AS
HIRE_DATE_YMININTERVAL
FROM EMPLOYEES
WHERE department_id = 20;
-------------------------------------------------------------------------------
MSN：[email protected]
My blog：http://blog.itpub.net/toms_zhang
2005 年4 月25 日
-------------------------------------------------------------------------------
Access的这个DateSerial的功能很强大，参数是三个整数，然后攒出一个日期来，能很方便的实现日期计算，月末月初计算等等。http://www.itcankao.cn/ShowArt/Art_96.html而Oracle的日期函数里还没有这么一个直接从三个整数攒出一个时间的函数。