Oracle Date format conversion, ToChar (), ToDate ()

Oracle Date format Conversion

This article mainly describes date conversions in Oracle.

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;

This article from: http://www.jb51.net/article/45591.htm

Http://www.cnblogs.com/xiaobaihome/archive/2012/03/14/2395568.html

Oracle Date format conversion, ToChar (), ToDate ()