Oracle Date function

First, the common date data format

1. Get the last digit of the year, two-bit, three-bit, four-bit

Select To_char (sysdate, ' Y ') from dual; --Get the last one of the year

Select To_char (sysdate, ' YY ') from dual; --Get the last two digits of the year

Select To_char (sysdate, ' YYY ') from dual; --Get the last three digits of the year

Select To_char (sysdate, ' YYYY ') from dual; --Get the last four digits of the year

2. Get the current quarter

Select To_char (sysdate, ' Q ') from dual; --1 ~ March for the first quarter, 2 for the second quarter.

3. Get the number of months

Select To_char (sysdate, ' MM ') from dual; --May to 05

4. Get the Roman representation of the month

Select To_char (sysdate, ' RM ') from dual; --May for V

5. Get month names in 9-character lengths

Select To_char (sysdate, ' Month ') from dual;--May to May
 
6. Get the week of the year
 
Select To_char (sysdate, ' WW ') from dual; --May 20, 2014 is the 20th week of 2014
 
7. Get the first weeks of the month
 
Select To_char (sysdate, ' W ') from dual ; -May 20, 2014 is the 3rd week of May
 
8. For the day of the year
 
Select To_char (sysdate, ' DDD ') from dual;- -May 20, 2014 is the 140th day of 2014
 
9. Get the day of the month
 
Select To_char (sysdate, ' DD ') from Dual -May 20, 2014 is the 20th day of May
 
10. Get the day of the week
 
Select To_char (sysdate, ' D ') from dual;- -May 20, 2014 is the third day of the week (starting from Sunday)
 
11. Week for Chinese:
 
Select To_char (sysdate, ' DY ') from Dual --May 20, 2014 for Tuesday
 
12. Get 12 decimal hours
 
Select To_char (sysdate, ' HH ') from dual;- -22:36 minutes with 12-hour clock for 10 points
 
13. Get 24 Decimal hours
 
Select To_char (sysdate, ' HH24 ') from Dual --22:36 minutes with 24-hour clock for 22 points

Second, the usual time function

1. trunc (d, [?] ）

Select Sysdate S1,--Returns the current date, sometimes seconds

Trunc (sysdate) S2,--return to current date, no time seconds

Trunc (Sysdate, ' Year ') year,--return to the current January 1, minutes and seconds

Trunc (sysdate, ' Month ') month,--return to the 1st of the current month, no time or seconds

Trunc (Sysdate, "Day") day,--return to the current week of Sunday, no time or seconds

Trunc (sysdate, ' Q ') QUARTER,--return to the current quarter of 1st, no time seconds

Trunc (sysdate, ' D ') WEEK--return to the current week of Sunday, no time or seconds

2. Round (d, [?] ) rounded to the nearest date

Select Sysdate S1,

Round (sysdate) S2,

Round (sysdate, ' Year ') year,--rounded to the nearest 2014/1/1

Round (sysdate, ' Month ') month,--rounded to the nearest month 2014/6/1

Round (sysdate, ' Day ') day--rounded to the nearest Sunday 2014/5/18

3. Last_day (d) Get the date of the last day of the month containing D

Select Last_day (sysdate) from dual; --Get the last day of the month: 2014/5/31 22:46:01

4. Add_months (d, N) Date d pushes backwards n months

Select Add_months (sysdate,2) from dual; --Date backwards 2 months: 2014/7/20 22:49:36

5. Next_day (d, Day)

Select Next_day (sysdate,2) from dual; --What date is the 2nd day of the specified week in the first week after the date sysdate

6. Months_between (f,s) Date of difference between F and S

Select Months_between (sysdate,to_date (' 2007-04-12 ', ' Yyyy-mm-dd ')) from dual; --85.2889874551971

7. Get the number of days between two dates

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

Iii. Examples of Oracle date-time functions

1. Get last day of last month

Select To_char (Add_months (Last_day (sysdate), -1), ' Yyyy-mm-dd ') Lastday from dual;

2. Get last month's Today

Select To_char (Add_months (sysdate,-1), ' Yyyy-mm-dd ') pretoday from dual;

3. Get the first day of the month

Select To_char (Add_months (Last_day (sysdate) +1,-2), ' Yyyy-mm-dd ') FirstDay from dual;

4. Obtain a specific date for all Friday of the month

Select To_char (B.A, ' yy-mm-dd ')

From (select Trunc (sysdate, ' mm ') + Rownum-1 A

where RowNum < b

where To_char (B.A, ' day ') = ' Friday ';

5. Find the number of days between 2002-02-28 and 2002-02-01 except Monday and seven

Select COUNT (*)

From (select Rownum-1 row_num

From All_objects

where RowNum <= to_date (' 2002-02-28 ', ' yyyy-mm-dd ')-

To_date (' 2002-02-01 ', ' yyyy-mm-dd ') + 1)

where To_char (to_date (' 2002-02-01 ', ' yyyy-mm-dd ') + row_num-1, ' D ') Not in (' 1 ', ' 7 ')

Oracle Date function