Oracle Database date format conversion operation

1. Date converted to string (as of October 20, 2016)

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') Strdatetime from dual; --Get year-month-day time: minutes: seconds
--Display the result: 2016-10-20 12:35:21

Select To_char (sysdate, ' yyyymmdd hh24:mi:ss ') Strdatetime from dual; --Acquisition date: minutes: Seconds
-Display Result: 20161020 13:39:25

Select To_char (sysdate, ' YYYYMMDD ') strdatetime from dual; --Date of acquisition
--Display Result: 20161020

Select To_char (sysdate, ' yyyy ') stryear from dual; --Get year
--Display Result: 2016

Select To_char (sysdate, ' mm ') Strmonth from dual; --Get the month
--Display Result: 10

Select To_char (sysdate, ' DD ') strday from dual; --Acquisition Day
--Display Result: 20

Select To_char (sysdate, ' hh24 ') Strhour from dual; --When acquired
--Display Result: 13

Select To_char (sysdate, ' mi ') Strminute from dual; --Get points
--Display Result: 46

Select To_char (sysdate, ' SS ') Strsecond from dual; --Get seconds
--Display Result: 43

2. String and Time transfer

Select To_date (' 2010-10-20 13:23:44 ', ' yyyy-mm-dd hh24:mi:ss ') DateTime from dual;
Display results: 2010/10/20 13:23:44

Select To_date (' 2010-10-20 13:23:44 ', ' yyyy/mm/dd hh24:mi:ss ') DateTime from dual;

Display results: 2010/10/20 13:23:44

Select To_char (to_date (222, ' J '), ' JSP ') from dual;

Displaying results: Hundred Twenty-two

If you follow the example below, you will get an error: ORA-01849: The hour value must be between 1 and 12. (Because the HH is 12 binary, no 13 error)

Select To_date (' 2005-12-25,13:25:59 ', ' Yyyy-mm-dd,hh:mi:ss ') from dual;

3. Query the day of the week

Select To_char (to_date (' 2012-10-20 ', ' yyyy-mm-dd '), ' Day ') strday from dual;
displaying results: Saturday

Select To_char (to_date (' 2012-10-20 ', ' yyyy-mm-dd '), ' Day ', ' nls_date_language = 中文版 ') strday from dual;
displaying results: Saturday

4. Number of days between two dates

Select Floor (sysdate-to_date (' 20161010 ', ' YYYYMMDD ')) Strtime from dual;

--Where SYSDATE=2016/10/20 17:10:51
--Display Results: 10

5. The use of time null

Select To_date (null) from dual;

6. Month Difference

Select Months_between (Date ' 2014-04-23 ', Date ' 2013-04-23 ') days from dual;

Oracle Database date format conversion operation