Oracle Date-Time Function summary

Students who write SQL often should be exposed to some of the Oracle's datetime functions, such as financial software or human resources software that is counted annually, quarterly, monthly, or even every week.

Today is nothing to do, deliberately from the online collation of some information, for future inspection.

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 for 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 few weeks of the month

Select To_char (sysdate, ' W ') from dual; -May 20, 2014 is the 3rd week of May

8. Get 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. Get Chinese for the week

Select To_char (sysdate, ' DY ') from dual; -- May 20, 2014 for Tuesday

12. Get 12 number of hours in the system

Select To_char (sysdate, ' HH ') from dual; --22:36 minutes with 12-hour clock for 10 points

13. Get 24 number of hours in the system

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,--Returns the current date, minutes and seconds       trunc (sysdate, ' Year ') year,--Returns the January 1 of the current years, no time Seconds       trunc (sysdate, ' Month ') month,--Returns the current month of 1st, minutes and minutes       trunc (sysdate, ' Day '),--Returns the current week of Sunday, minutes and seconds       Trunc ( Sysdate, ' Q ') QUARTER,--Returns the current quarter of 1st,       trunc (sysdate, ' D ') WEEK--Returns the current week of Sunday, minutes and minutes from  dual

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

Select Sysdate S1,       round (sysdate) S2,       round (sysdate, ' year ') years,--rounded to the nearest year 2014/1/1       round (sysdate, ' Month ') month,-rounded to the nearest month 2014/6/1       round (sysdate, ' Day ')-rounded to the nearest Sunday 2014/5/18 from  dual

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. Comprehensive usage

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 from          dba_objects         where Rownu M < 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 ', ' yy Yy-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 ')