Summary of oracle Date and Time Functions

Today, I am free to wait. I specially sorted out some information from the Internet for future reference.

I. Common date data formats

1. Obtain the last digit, two digits, three digits, and four 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 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. Obtain the current quarter
Select to_char (sysdate, 'q') from dual; -- 1 ~ March is the first quarter, and 2 represents the second quarter.

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

4. Obtain the Rome representation of the month
Select to_char (sysdate, 'rm ') from dual; -- May is V.

5. Get the name of the month expressed in length of 9 Characters
Select to_char (sysdate, 'month') from dual; -- May is May

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

7. Obtain the week number of the month
Select to_char (sysdate, 'w') from dual; -- May 20, 2014 is 3rd week

8. Obtain the day of the current year
Select to_char (sysdate, 'ddd ') from dual; -- January 1, 140th: January 1

9. Obtain the day of the month
Select to_char (sysdate, 'dd') from dual; -- January 1, 20th: January 1

10. Get the day of the week
Select to_char (sysdate, 'D') from dual; -- January 1, May 20, 2014 is the third day of the week (from Sunday)

11. Get Chinese week
Select to_char (sysdate, 'dy ') from dual; -- Tuesday, January 1, May 20, 2014

12. Obtain the 12-digit hour.
Select to_char (sysdate, 'hh') from dual; -- the hourly clock is set.

13. Get the 24-digit hour
Select to_char (sysdate, 'hh24') from dual; -- in the 24-hour format

Ii. Common time functions

1. trunc (d ,[? ])

Copy codeThe Code is as follows:
Select sysdate S1, -- returns the current date, with time, minute, and second
Trunc (sysdate) S2, -- returns the current date, no time, minute, second
Trunc (sysdate, 'Year') year, -- returns January 1, January 1 of the current YEAR.
Trunc (sysdate, 'month') month, -- returns the 1 day of the current MONTH, no hour, minute, second
Trunc (sysdate, 'day') day, -- returns Sunday of the current week, no hour, minute, second
Trunc (sysdate, 'q') QUARTER, -- returns the first day of the current QUARTER, no hour, minute, second
Trunc (sysdate, 'D') WEEK -- returns Sunday of the current WEEK, no hour, minute, second
From dual

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

Copy codeThe Code is as follows:
Select sysdate S1,
Round (sysdate) S2,
Round (sysdate, 'Year') year, -- round to the nearest YEAR
Round (sysdate, 'month') month, -- round to the nearest MONTH
Round (sysdate, 'day') day -- round to the nearest Sunday
From dual

3. last_day (d) gets the date of the last day of the month containing d

Select last_day (sysdate) from dual; -- Obtain the last day of the month: 22:46:01
4. add_months (d, n) date d is pushed back to n months

Select add_months (sysdate, 2) from dual; -- two months after the date is pushed: 22:49:36

5. next_day (d, day)

Select next_day (sysdate, 2) from dual; -- specifies the date of day 2nd in the first week after the date sysdate

6. months_between (f, s) date, number of different months between f and s

Select months_between (sysdate, to_date ('1970-04-12 ', 'yyyy-mm-dd') from dual; -- 2007

7. Get the number of days in the two-day period

Select floor (sysdate-to_date ('20140901', 'yyyymmdd') from dual;

Iii. Comprehensive Usage

1. Obtain the last day of the last month

Select to_char (add_months (last_day (sysdate),-1), 'yyyy-MM-dd') lastDay from dual;

2. Get today from last month

Select to_char (add_months (sysdate,-1), 'yyyy-MM-dd') preToday from dual;

3. Obtain the first day of last month

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

4. Obtain the specific dates of all Friday in a month.

Copy codeThe Code is as follows:
Select to_char (B. a, 'yy-MM-DD ')
From (select trunc (sysdate, 'mm') + rownum-1
From dba_objects
Where rownum <32) B
Where to_char (B. a, 'day') = 'Friday ';

5. Search for the days except Monday and seven between and

Copy codeThe Code is as follows:
Select count (*)
From (select rownum-1 row_num
From all_objects
Where rownum <= to_date ('2017-02-28 ', 'yyyy-mm-dd ')-
To_date ('1970-02-01 ', 'yyyy-mm-dd') + 1)
Where to_char (to_date ('1970-02-01 ', 'yyyy-mm-dd') + row_num-1, 'D') not in ('1', '7'