How to convert oracle date format

Recommended tutorial: Oracle tutorial

This article mainly introduces date conversion in Oracle.

1. Convert date to string (take October 20, 2016 as an example)

##select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') strDateTime from dual; --Get year-month-day hour:minute:second --The displayed result is: 2016- 10-20 12:35:21

select to_char(sysdate,'yyyymmdd hh24:mi:ss') strDateTime from dual; --Get year, month, day, hour: minute :Seconds --The displayed result is: 20161020 13:39:25

select to_char(sysdate,'yyyymmdd') strDateTime from dual; -- Get the year, month and day --The displayed result is: 20161020

select to_char(sysdate,'yyyy') strYear from dual; --Get the year --The displayed result is: 2016

select to_char(sysdate,'mm') strMonth from dual; --Get the month --The display result is: 10

select to_char(sysdate,'dd') strDay from dual; --Get the day --The display result is ：20

select to_char(sysdate,'hh24') strHour from dual; --When getting --The displayed result is: 13

select to_char(sysdate,'mi') strMinute from dual; --Get points --The displayed result is: 46

select to_char(sysdate,'ss') strSecond from dual; --Get seconds--The displayed result is: 43

2 . Convert string and time

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 result: 2010/10/20 13:23:44

select to_char( to_date(222,'J'),'Jsp') from dual;

Display results: Two Hundred Twenty-Two

If you write it according to the following example, it will Error: ORA-01849: hour value must be between 1 and 12. (Because hh is in hexadecimal and there is no 13, an error is reported)

select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh: mi:ss') from dual;

##3. Query the day of the week a certain day is##select to_char(to_date ('2012-10-20','yyyy-mm-dd'),'day') strDay from dual;

Display results: Saturday

select to_char (to_date('2012-10-20','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = English') strDay from dual;

Display Result: saturday

4. The 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 result: 10

5. Usage of null time

select to_date(null) from dual;

6. Month difference

select months_between(date'2014-04-23',date'2013-04-23 ') days from dual;

The above is the detailed content of How to convert oracle date format. For more information, please follow other related articles on the PHP Chinese website!