1.extract

从date或datetime类型值中导出年、月、日、day（这个月的第几日）、时、分、秒

语法入下图：

 

date类型可以EXTRACT 年月日和day

datetime可以EXTRACT 年月日和day及时分秒，date不可以EXTRACT 时分秒

 

例如：

C:\Documents and Settings\Administrator>sqlplus "/as SYSDBA"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 11 15:01:05 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select current_date,extract(day from current_date) day,extract(year from cu

rrent_date) year from dual;

CURRENT_D        DAY       YEAR

--------- ---------- ----------
11-OCT-12         11       2012

SQL> select SYSDATE,extract(hour  from SYSDATE) hour from dual;

select SYSDATE,extract(hour  from SYSDATE) hour from dual
                                  *
ERROR at line 1:
ORA-30076: invalid extract field for extract source-----date不可以EXTRACT 时分秒

SQL> SELECT EXTRACT(minute FROM TIMESTAMP '2010-10-10 10:10:10') FROM DUAL;

EXTRACT(MINUTEFROMTIMESTAMP'2010-10-1010:10:10')

------------------------------------------------
                                              10

2.to_char

将日期转化为字符串

格式：to_char(datetime,格式)或to_char(date,格式)

例如：

SQL> select to_char(current_date,'yyyymmdd') from dual;

TO_CHAR(

--------
20121011

 

3.to_date

将字符串转换为日期

格式：

fmt 参数：fmt是日期型要转换成的格式

nlsparam参数：指定转化后的字符串语言，格式是

'NLS_DATE_LANGUAGE = language'

；忽略时，为系统默认。

 

例如：

SQL> select to_date(to_char(current_date,'yyyymmdd'),'yyyymmdd','nls_date_langua

ge=American') from dual;

TO_DATE(T

---------
11-OCT-12

 

SQL> select to_date(to_char(SYSDATE,'yyyymmdd'),'yyyymmdd') from dual;

TO_DATE(T

---------
11-OCT-12