3、Oracle PL/SQL中Date格式及格式转换

该文章是 PL/SQL基础（3）：小专题 系列文章之一。

Oracle 插入日期（时间）时报错：ORA-01861:文字与格式字符串不匹配。这是由于插入的日期格式和数据库现有的日期格式不一致，解决的方法是需要to_date函数格式化待插入的日期。TO_CHAR(<date>,"<format>")要求指定date的格式（format）。首先了解下Oracle的Date类型的格式。

         Oracle中Date格式通常以下表的格式字符串和-/:组合而成。例如：

to_date("1998/05/31:12:00:00AM","yyyy/mm/dd:hh:mi:ssam")

表Oracle中Date类型格式字符串

MM	Numeric month (e.g., 07)
MON	Abbreviated month name (e.g., JUL)
MONTH	Full month name (e.g., JULY)
DD	Day of month (e.g., 24)
DY	Abbreviated name of day (e.g., FRI)
YYYY	4-digit year (e.g., 1998)
YY	Last 2 digits of the year (e.g., 98)
RR	Like YY, but the two digits are ``rounded"" to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM)	Meridian indicator
HH	Hour of day (1-12)
HH24	Hour of day (0-23)
MI	Minute (0-59)
SS	Second (0-59)

 

         一些常见格式的Date转换如下：

insert into tabname(datecol) value(sysdate);

insert into tabname(datecol)value(sysdate+1) ;

insert into tabname(datecol)value(to_date("2014-02-14","yyyy-mm-dd")) ;

insert into tabname(datecol)value(to_date("2014-02-14 20:47:00","yyyy-mm-dd hh24:mi:ss")) ;

insert into tabname(datecol)value(to_date("20140214","yyyymmdd")) ;

insert into tabname(datecol)value(to_date("20140214204700","yyyymmddhh24miss")) ;

 

         对于Date类型可以直接使用标准的运算符=, !=, >进行比较。另外，Date类型的操作函数包括：

S.N	Function Name & Description
1	ADD_MONTHS(x, y);  Adds y months to x.
2	LAST_DAY(x);  Returns the last day of the month.
3	MONTHS_BETWEEN(x, y);  Returns the number of months between x and y.
4	NEXT_DAY(x, day);  Returns the datetime of the next day after x.
5	NEW_TIME;  Returns the time/day value from a time zone specified by the user.
6	ROUND(x [, unit]);  Rounds x;
7	SYSDATE();  Returns the current datetime.
8	TRUNC(x [, unit]);  Truncates x.

 

 

引用：

http://www.tutorialspoint.com/plsql/plsql_date_time.htm

http://blog.csdn.net/wyzxg/article/details/2729507

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm