Time operations in Oracle and to_char and to_date usage Summary

In Oracle, the time operations and to_char and to_date usage are summarized. The so-called "with connector" means there is no connector between yyyy, mm, and dd of the first parameter of to_date.

In Oracle, the time operations and to_char and to_date usage are summarized. The so-called "with connector" means there is no connector between yyyy, mm, and dd of the first parameter of to_date.

OracleTime Processing sometimesTo_charAndTo_dateThe two key functions are summarized here.:

IObtain the current system time

Select sysdate from dual;

Select to_char (sysdate, 'yyyy. mm. dd') from dual
Select to_char (sysdate, 'yyyy-mm-dd') from dual
Select to_char (sysdate, 'yyyy mm dd') from dual
Select to_char (sysdate, 'yyyy * mm * dd') from dual
Select to_char (sysdate, 'yyyymmdd') from dual

As long as the conversion is a date,Yyyy, mm, ddYou can add any connector without a connector in the middle. We can regard space as an empty connector.

View:

To_dateA little more.

①With Connector

Select to_date ('1970. 123', 'yyyy-mm-dd') from dual
Select to_date ('1970. 123', 'yyyy. mm. dd') from dual
Select to_date ('1970. 123', 'yyyy mm dd') from dual

Select to_date ('1970-07-26 ', 'yyyy-mm-dd') from dual
Select to_date ('1970-07-26 ', 'yyyy. mm. dd') from dual
Select to_date ('1970-07-26 ', 'yyyy mm dd') from dual

Select to_date ('1970. 123', 'yyyy-mm-dd') from dual
Select to_date ('1970. 123', 'yyyy. mm. dd') from dual
Select to_date ('1970. 123', 'yyyy mm dd') from dual

The so-called connector is ours.To_dateThe first parameterYyyy, mm, ddThere is no connector between them. If yes, the second parameter must have a connector. AndMm, ddThe number of digits is not required.07Can correspondMm, 7It can also correspondMm. We can regard space as an empty connector.

②Without a connector

Select to_date ('20140901', 'yyyymmdd') from dual
Select to_date ('123', 'yyyy. mm. dd') from dual
Select to_date ('123', 'yyyy-mm-dd') from dual

A string that does not contain a connector must have one-to-one digits. A second string can contain a connector that does not contain a connector.07YesMm,7Cannot matchMm

ThuTransition with time

Select to_char (sysdate, 'yyyy. mm. dd hh24 mi ss') from dual

:

Select to_date ('1970. 2012 21:09:09 ', 'yyyy-mm-dd hh24 mi ss') from dual

:

In addition to the display of time, the other principles are the same as those described above.

V.Project applications

We usedJavaThe time in the code can be handed overSQLStatement

Update student set birth = to_date (# birth #, "yyyy-mm-dd ");

Just send a date parameter, and you will see whether you have a connector or not.