Oracle Date format processing Rollup __oracle

Date and Time format application To_char (date, formatting parameters)

1, returns any valid delimiter concatenation the month day string

<pre name= "code" class= "SQL" ><span style= "font-size:18px;" >1.1, Select to_char (sysdate, ' Yyyy/mm/dd ') from dual;
2015/04/29 (that is, return a string that is connected with a '/' delimiter, or it can be replaced with a ' ^ ' connection, the result is 2015^04^29, or it can be replaced by '-', the result is 2015-04-29)

1.2. Select to_char (sysdate, ' Yyyy/mm/dd hh:mm:ss ') from dual;
2015/04/29 03:04:40

1.3, Select to_char (sysdate, ' AD yyyy-mm-dd hh:mm:ss ') from Dual;
A.D. 2015-04-29 03:04:33

1.4, Select to_char (sysdate, ' BC yyyy-mm-dd hh:mm:ss ') from dual;
A.D. 2015-04-29 03:05:53

1.5, Select to_char (sysdate, ' Yyyy-mm-dd PM hh24:mi: SS ') from Dual;/**hh12:12-hour **/
****************************
2015-04-29 pm 15:12:21</span>

Second, get the corresponding data of the date string

2.1, date conversion for the corresponding century CC/SCC (s prefix specified as in the event of the show of BC, add (-) Prompt before display: * If the last two digits in the year are between 01 and 99 (including), the return value equals the first two digits of the year +1 * if the last two digits in the year are 00, the return value is the The same as the first two digits of the year) Select To_char (to_date (' 2001-8-3 ', ' yyyy-mm-dd '), ' cc ') | |
' Century ' from dual; 21st century 2.2, the value of the specified date in the week D (range: 1-7) Select ' Week ' | | (Case To_char (to_date (' 2015-04-05 ', ' yyyy-mm-dd '), ' D ') when ' 1 ' then ' Day ' else To_char (to_char (' 2015-04-05 ', ' Yyyy-mm-dd '), ' D ')-1) End "from dual;/* is the Gregorian calendar of Sunday, where the return of 1, then the corresponding return of Wednesday is 4, so do the next deal * * ************************** Sunday 2.3, Specifies the number of days in the corresponding month DD (1-31), DDD Current year, days of day current week (direct return to week x) Select To_char (to_date (' 2015-04-01 ', ' yyyy-mm-dd '), ' DD ') from
Dual
01 2.4, abbreviated month of the specified date, Select to_char (sysdate, ' MON ') from dual;
April 2.5, Year/syear Returns the character year, where s prefix is the same as the SCC, Select To_char (Sysdate, ' year ') from dual; Twenty fifteen 2.6, YYYY returns the number year of the specified date, MM returns the month of the specified date (01-12), Am hh12/am hh24, MI Returns the minute (00-59) of the specified date, SS returns the second of the specified date (00-59), the SS returns the second of the specified date (00-59), the y,yyy returns a comma-delimited display of year 2,015, and the y/yy/yyy returns a date with a specified length of 2.7, Q return the quarter of the specified date (range: 1-4), return the specified dateThe X Week (range: 1-5), WW returns the specified date in the year of the X Week (range: 1-53), J from January 1, 4712 to the specified date of the total number of days, sssss return from midnight to a specified time elapsed seconds (range: 0-86399) Select ' s ' | | To_char (to_date (' 2015-04-09 ', ' yyyy-mm-dd '), ' W ') | |
Zhou ' from dual;
Week 2nd 2.8, DL Returns a long date form based on the format of the parameters in the data, the DS returns the same short date form as above (2015-04-29), and the TS returns the time (3:55:31), and Dy returns the day of the Week (Wednesday)
Select to_char (sysdate, ' DL ') from dual; Wednesday, April 29, 2015

Three, simple application

3.1, get the specified date in the same week, Monday date (+2 is to get Monday, +3 is Tuesday, and so on the largest plus 8 is the current week Sunday, if plus 9 is next week Monday)
Select to_date (' 2015-04-02 ', ' Yyyy-mm-dd ')-To_char (to_date (' 2015-04-02 ', ' yyyy-mm-dd '), ' d ') + 2 from
dual;
2015-03-30
 
3.2, return to the year of the corresponding date of xxx
Select to_date (', ' DDD ') from dual;
2015-01-10 

3.3 Current time minus 7 minutes of time 
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual; 
The current time minus 7 hours of time 
select Sysdate-interval ' 7 ' hour from dual; 
The current time minus 7 days 
select Sysdate-interval ' 7 ' from dual; 
Current time minus July time 
select Sysdate,sysdate-interval ' 7 ' month from dual; 
The current time minus 7 years of time 
select Sysdate,sysdate-interval ' 7 ' year from dual; 
Time interval multiplied by a number 
select Sysdate,sysdate-8*interval ' 7 ' hour from dual;