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	LikeYY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus,06is considered2006instead of1906
AM(orPM)	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 nextdayafter 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

更多相关文章

随机推荐