Oracle Time function (sysdate) in-depth understanding

addition
Select Sysdate,add_months (sysdate,12) from dual; --plus 1 years
Select Sysdate,add_months (sysdate,1) from dual; --Add January
Select Sysdate,to_char (sysdate+7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 weeks
Select Sysdate,to_char (sysdate+1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 days
Select Sysdate,to_char (sysdate+1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 hours
Select Sysdate,to_char (sysdate+1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 minutes
Select Sysdate,to_char (sysdate+1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 seconds

Subtraction
Select Sysdate,add_months (sysdate,-12) from dual; -Minus 1 years
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 weeks
Select Sysdate,to_char (sysdate-1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 days
Select Sysdate,to_char (sysdate-1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 hours
Select Sysdate,to_char (sysdate-1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 minutes
Select Sysdate,to_char (sysdate-1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 seconds

A brief analysis of Oracle time function (sysdate)
1: Get the current date is the week of the month
Sql> Select To_char (sysdate, ' YYYYMMDD W HH24:MI:SS ') from dual;
To_char (sysdate, ' YY
-------------------
20030327 4 18:16:09
Sql> Select To_char (sysdate, ' W ') from dual;
T
-
4
2: Get the current date is the day of the one week, note that Sunday is the first
Sql> Select Sysdate,to_char (sysdate, ' D ') from dual;
Sysdate T
--------- -
27-MAR-03 5
Similar:
Select To_char (sysdate, ' yyyy ') from dual; -year
Select To_char (sysdate, ' Q ' from dual;--season
Select To_char (sysdate, ' mm ') from dual; --month
Select To_char (sysdate, ' DD ') from dual; --Day
The day of the DDD year
The first few weeks of the WW year
W the first few weeks of the month
Day of the week in D week
HH hours (12)
Hh24 hours (24)
Mi points
SS sec
3: Take the current date is the day of the week in Chinese display:
Sql> Select To_char (sysdate, "Day") from dual;
To_char (sysdate, ' Day ')
----------------------

Thursday
4: If a table has an index on a field of date type, 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 the day 0:0 A.M. 0 seconds
Select Trunc (sysdate) from dual;
--Get the last second of the day
Select Trunc (sysdate) + 0.99999 from dual;
--Get the exact number of hours
Select Trunc (sysdate) + 1/24 from dual;
Select Trunc (sysdate) + 7/24 from dual;
7. Get the date of tomorrow 0:0 A.M. 0 seconds
Select Trunc (sysdate+1) from dual;
Select Trunc (sysdate) +1 from dual;
8: Date of the month
Select Trunc (sysdate, ' mm ') from dual;
9: Get the day of next month
Select Trunc (Add_months (sysdate,1), ' mm ') from dual;

10: Return 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: Determine whether a day is the year of the run year
Select Decode (To_char (Last_day (trunc (sysdate, ' y ') +31), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;
15: Judge whether the year after two years
Select Decode (To_char (Last_day (Add_months (sysdate,24), ' y '), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;
16: Get the date of the quarter
Select Ceil (To_number (To_char (sysdate, ' mm '))/3) from dual;
Select To_char (sysdate, ' Q ') from dual;

Oracle Time function (sysdate) in-depth understanding