Oracle date operations

This article has found many related books and found nearly 30 common Oracle date operations, from basic year, month, and day representation to slightly more complex days from the beginning of the year to the present, the workday calculation method, which is more difficult, involves a comprehensive solution.

I. Common Oracle date operations
1. to_char (sysdate, 'q') quarter
2. to_char (sysdate, 'yyyy') year
3. to_char (sysdate, 'mm') month
4. to_char (sysdate, 'dd ')
5. to_char (sysdate, 'D') day of the week
6. to_char (sysdate, 'day') DAY of the week
7. to_char (sysdate, 'ddd ') day of the year
8. extract (year from query_cxrq) year
9. extract (month from query_cxrq) month
10. extract (day from query_cxrq)
Example: select extract (month from sysdate) from dual

Ii. Simple Oracle date operations
1. date at the end of last month: select last_day (add_months (sysdate,-1) from dual;
2. last second of the month: select trunc (add_months (sysdate, 1), 'mm')-1/24/60/60 from dual
3. Date of Monday of this week: select trunc (sysdate, 'day') + 1 from dual
4. days from the beginning of the year to date: select ceil (sysdate-trunc (sysdate, 'Year') from dual;
5. Today is the week of this year: select to_char (sysdate, 'fmww') from dual
6. today is the week of the month: SELECT TO_CHAR (SYSDATE, 'ww ')-TO_CHAR (TRUNC (SYSDATE, 'mm'), 'ww ') + 1 AS "weekOfMon" FROM dual
7. days of the month: SELECT to_char (last_day (SYSDATE), 'dd') days FROM dual
8. Number of days this year: select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual
9. Date of next Monday: SELECT Next_day (SYSDATE, 'monday') FROM dual

10. The latency of sdate + (5/24/60/60) based on the system time is 5 seconds.
11. latency of sdate + 5/24/60 based on system time is 5 minutes
12. latency of sdate + 5/24 based on system time is 5 hours
13. sdate + 5 delay of 5 days based on system time
14. d_months (sysdate,-5) Delay of May on the basis of system time
15. d_months (sysdate,-5*12) delay of 5 years based on system time
16. days interval:
The number of days in the two-day period (the date minus the number of days)
Select floor (sysdate-to_date ('20140901', 'yyyymmdd') from dual;

Two operators in Oracle date operationsMethod

1. Workday Calculation Method
Create table t (s date, e date );
Alter session set nls_date_format = 'yyyy-mm-dd ';
Insert into t values ('2017-03-01 ', '2017-03-03 ');
Insert into t values ('2017-03-02 ', '2017-03-03 ');
Insert into t values ('2017-03-07 ', '2017-03-08 ');
Insert into t values ('2017-03-07 ', '2017-03-09 ');
Insert into t values ('2017-03-05 ', '2017-03-07 ');
Insert into t values ('2017-02-01 ', '2017-03-31 ');
It is assumed that all dates do not contain time; otherwise, you can add trunc before all dates.

2. Determine whether the current time is morning afternoon or evening
SELECT CASE
WHEN to_number (to_char (SYSDATE, 'hh24') BETWEEN 6 AND 11 THEN 'am'
WHEN to_number (to_char (SYSDATE, 'hh24') BETWEEN 11 AND 17 THEN 'more'
WHEN to_number (to_char (SYSDATE, 'hh24') BETWEEN 17 AND 21 THEN''
END
FROM dual;

1. Use triggers to implement Oracle operation logs
2. Common Oracle date functions
3. Use and Maintenance of Oracle Database Password files
4. How to use Oracle password expiration and profile
5. Roles and descriptions of Oracle password files