Oracle Time function (sysdate) deep understanding of _oracle

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

Brief analysis of Oracle time function (sysdate)
1: The current date is the week ordinal of this 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: The current date is the day of one weeks, 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 first day of the DDD year
The first few weeks of the WW year
W the first few weeks of the month
Days of the week in D week
HH Hour (12)
Hh24 hours (24)
Mi min
SS seconds
3: Take the current date is the week several Chinese display:
Sql> Select To_char (sysdate, ' Day ') from dual;
To_char (sysdate, ' Day ')
----------------------

Thursday
4: If a table is indexed in a date-type field, how to use the
Alter session set nls_date_format= ' Yyyy-mm-dd HH24:MI:SS '

5: Get the current date
Select Sysdate from dual;
6: 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 to 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 this year
SELECT to_char (sysdate, ' DDD ') from DUAL;
13: How to add 2 years to the existing date
Select Add_months (sysdate,24) from dual;
14: To determine whether a certain day is the year of the run year
Select Decode (To_char (Last_day (trunc (sysdate, ' y ') +31), ' DD '), ' 29 ', ' Leap Year ', ' excepting ') from dual;
15: Determine whether two years after the run year
Select Decode (To_char (Last_day (Add_months (sysdate,24), ' y ') +31, ' DD '), ' 29 ', ' Leap Year ', ' excepting ') from dual;
16: Getting the date of the quarter
Select Ceil (To_number (To_char (sysdate, ' mm '))/3) from dual;
Select To_char (sysdate, ' Q ') from dual;