该文章是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://www.jb51.cc/article/p-szvponlf-kr.html
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm