Summary of oracle Date and Time Functions

Those who often write SQL should be exposed to oracle date and time functions. For example, financial software or human resources software should be collected every year, quarter, month, or even Week.

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 ,[? ])

Select sysdate S1, -- returns the current date, with hour, minute, second, trunc (sysdate) S2, -- returns the current date, without hour, minute, second, trunc (sysdate, 'Year') year, -- returns the month of the current year, with no hour, minute, second, trunc (sysdate, 'month') MONTH, -- returns the day of the current month, without hour, minute, second, trunc (sysdate, 'day') DAY, -- returns Sunday of the current week, trunc (sysdate, 'q') QUARTER without hour and minute, -- returns the first day of the current QUARTER, and trunc (sysdate, 'D') without hour and minute ') WEEK -- returns the Sunday of the current WEEK, with no hour, minute, and second from dual.

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

Select sysdate S1, round (sysdate) S2, round (sysdate, 'Year') year, -- round to 2014/1/1 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.

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') = 'Friday ';

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

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'