In-depth understanding of ORACLE time functions (SYSDATE)

Addition
Select sysdate, add_months (sysdate, 12) from dual; -- add 1 year
Select sysdate, add_months (sysdate, 1) from dual; -- add January
Select sysdate, to_char (sysdate + 7, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 week
Select sysdate, to_char (sysdate + 1, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 day
Select sysdate, to_char (sysdate + 1/24, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 hour
Select sysdate, to_char (sysdate + 1/24/60, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 minute
Select sysdate, to_char (sysdate + 1/24/60/60, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 second

Subtraction
Select sysdate, add_months (sysdate,-12) from dual; -- minus 1 year
Select sysdate, add_months (sysdate,-1) from dual; -- minus January
Select sysdate, to_char (sysdate-7, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 week
Select sysdate, to_char (sysdate-1, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 day
Select sysdate, to_char (sysdate-1/24, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 hour
Select sysdate, to_char (sysdate-1/24/60, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 minute
Select sysdate, to_char (sysdate-1/24/60/60, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 second

Analysis of ORACLE time functions (SYSDATE)
1: Obtain the week number of the current month.
SQL> select to_char (sysdate, 'yyyymmdd W HH24: MI: ss') from dual;
TO_CHAR (SYSDATE, 'yy
-------------------
4 18:16:09 20030327
SQL> select to_char (sysdate, 'w') from dual;
T
-
4
2: The current date is the day of the week. Note that Sunday is the first day of the week.
SQL> select sysdate, to_char (sysdate, 'D') from dual;
SYSDATE T
----------
27-MAR-03 5
For example:
Select to_char (sysdate, 'yyyy') from dual; -- year
Select to_char (sysdate, 'q' from dual; -- quarter
Select to_char (sysdate, 'mm') from dual; -- month
Select to_char (sysdate, 'dd') from dual; -- day
The day of the year in ddd
The week in WW
W the week of the month
D. day of the week
Hh hour (12)
Hh24 (24)
Mi score
Ss seconds
3: display the current date in Chinese on the day of the week:
SQL> select to_char (sysdate, 'day') from dual;
TO_CHAR (SYSDATE, 'day ')
----------------------

Thursday
4: If a table is indexed on a date field, how to use
Alter session set NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: ss'

5: Get the current date
Select sysdate from dual;
6: Get the date of 00:00:00 that day
Select trunc (sysdate) from dual;
-- Get the last second of the day
Select trunc (sysdate) + 0.99999 from dual;
-- Obtain the hour value.
Select trunc (sysdate) + 1/24 from dual;
Select trunc (sysdate) + 7/24 from dual;
7. Get the date of 00:00:00 tomorrow morning.
Select trunc (sysdate + 1) from dual;
Select trunc (sysdate) + 1 from dual;
8: date of January 1, 1st day of this month
Select trunc (sysdate, 'mm') from dual;
9: Get the date of January 1, 1st day of next month.
Select trunc (add_months (sysdate, 1), 'mm') from dual;

10: returns the last day of the current month?
Select last_day (sysdate) from dual;
Select last_day (trunc (sysdate) from dual;
Select trunc (last_day (sysdate) from dual;
Select trunc (add_months (sysdate, 1), 'mm')-1 from dual;
11: Get every day of the year
Select trunc (sysdate, 'yyyy') + rn-1 date0
From
(Select rownum rn from all_objects
Where rownum <366 );
12: Today is the nth day of the year
SELECT TO_CHAR (SYSDATE, 'ddd ') from dual;
13: how to add 2 years to an existing date
Select add_months (sysdate, 24) from dual;
14: determines whether the year of a certain day is a runyear.
Select decode (to_char (last_day (trunc (sysdate, 'y') + 31), 'dd'), '29', 'leap year', 'Year') from dual;
15: judge whether it is a runyear after two years
Select decode (to_char (last_day (trunc (add_months (sysdate, 24), 'y') + 31), 'dd'), '29', 'leap year ', 'Year') from dual;
16: Obtain the quarter of the date
Select ceil (to_number (to_char (sysdate, 'mm')/3) from dual;
Select to_char (sysdate, 'q') from dual;