Summary of usage of oracle date time function

Today, I have nothing to do, so I specially sorted out some information from the Internet for future reference.

1. Common date data formats

1. Get the last 1, 2, 3, 4 digits of the year

select to_char (sysdate, 'Y') from dual; --Get the last digit of the year
select to_char (sysdate, 'YY') from dual; --Get the last two places 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

Step 2 Get current quarter
select to_char (sysdate, 'Q') from dual; --January to March is the first quarter, and 2 is the second quarter.

3. Get the number of months
select to_char (sysdate, 'MM') from dual; -May is 05

4. Get the Roman representation of the month
select to_char (sysdate, 'RM') from dual; --May is V

5. Get a 9-character month name
select to_char (sysdate, 'Month') from dual; -May is May

6. Obtain week of the year
select to_char (sysdate, 'WW') from dual; -May 20, 2014 is the 20th week of 2014

7. Get week of the month
select to_char (sysdate, 'W') from dual; -May 20, 2014 is the third 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 (from Sunday)

11. Week of Acquiring Chinese
select to_char (sysdate, 'DY') from dual; -May 20, 2014 is Tuesday

12. Get the number of hours in 12
select to_char (sysdate, 'HH') from dual; --At 22:36, it is timed at 10 o'clock on the 12-hour system

13. Get 24-ary hours
select to_char (sysdate, 'HH24') from dual; -22:36 is timed at 22 o'clock on the 24-hour system

2. Commonly used time functions

1. trunc(d, [ ? ])

select sysdate S1, --  Returns the current date , Sometimes minutes and seconds  
       trunc(sysdate) S2, --  Returns the current date , No minutes and seconds  
       trunc(sysdate, 'year') YEAR, --  Returns the of the current year 1 Month 1 Day , No minutes and seconds  
       trunc(sysdate, 'month') MONTH, --  Returns the of the current month 1 Day , No minutes and seconds  
       trunc(sysdate, 'day') DAY, --  Returns the Sunday of the current week , No minutes and seconds  
       trunc(sysdate, 'Q') QUARTER, --  Returns the current quarter's 1 Day , No minutes and seconds  
       trunc(sysdate, 'D') WEEK --  Returns the Sunday of the current week , No minutes and seconds  
  from dual

2. round (d, [? ]) To the nearest date

select sysdate S1, 
       round(sysdate) S2, 
       round(sysdate, 'year') YEAR, --  Rounding to the nearest year  2014/1/1 
       round(sysdate, 'month') MONTH, --  Rounding to the nearest month  2014/6/1 
       round(sysdate, 'day') DAY --  Rounding to the nearest Sunday  2014/5/18 
  from dual

3. last_day (d) Gets the date of the last day of the month that contains d

select last_day (sysdate) from dual; --Get the last day of this month: 2014/5/31 22:46:01
4. add_months (d, n) Date d is pushed back by n months

select add_months (sysdate, 2) from dual; --The date is pushed back by 2 months: 2014/7/20 22:49:36

5. next_day(d, day)

select next_day (sysdate, 2) from dual; --What is the second day of the specified week in the first week after the date sysdate

6. months_between (f, s) Number of months 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 in a two-day period

select floor(sysdate - to_date('20140405','yyyymmdd')) from dual;

3. Comprehensive usage

1. Get the 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 Day 1 of Last Month

select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;

4. Get the specific dates of all weekdays in a month

select to_char(b.a, 'YY-MM-DD') 
  from (select trunc(sysdate, 'mm') + rownum - 1 a 
          from dba_objects 
         where rownum < 32) b 
 where to_char(b.a, 'day') = ' Week 5';

5. Find days between 2002-02-28 and 2002-02-01 except Monday and July

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'