该文章是 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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
