Oracle Date-time functions

Students who write SQL frequently should be exposed to Oracle's date-time functions, such as: financial software or HR software needs to be counted every year, quarterly, monthly, or even weekly.

Today free to do nothing, deliberately collated some information from the Internet for future inspection.

First, the usual date data format

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

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 current quarter

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

3. Get the number of months

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

4. Access to the Roman representation of the month

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

5. Get the month name that is represented by a length of 9 characters

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

6. Get the first few weeks of the year

Select To_char (sysdate, ' WW ') from dual; --May 20, 2014 20th Week of 2014

7. Get the first few weeks of this month

Select To_char (sysdate, ' W ') from dual; --May 20, 2014 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 ordinal 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 (since Sunday)

11. Week to get Chinese

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 to 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 to 22 points

Second, commonly used time function

1. trunc (d, [?])

Select Sysdate S1,--Returns the current date, sometimes minutes  
       trunc (sysdate) S2,--Returns the current date, minutes and seconds  
       trunc (sysdate, ' year '),--Returns the January 1 of the current years, no time Minutes  
       trunc (sysdate, ' month ') month--return to the current month of 1st, time and minutes  
       trunc (sysdate, ' Day ') day,--return to the current week of Sunday, time and minutes  
       trunc ( Sysdate, ' Q ') quarter--return to the current quarter of 1st, time and minutes  
       trunc (sysdate, ' D ') WEEK--return to the current week of Sunday, minutes and seconds from  
  dual

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

Select Sysdate S1,  
       round (sysdate) S2,  
       round (sysdate, ' year '), and--rounded to the nearest 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) Obtain the date of the last day of the month containing D

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

4. Add_months (d, N) Date D push back n months

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

5. Next_day (d, Day)

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

6. Months_between (f,s) date between F and S number of months

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 today of last month

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

3. Get the first day of last 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 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 ', ' 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 ')

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/