Oracle Time function (sysdate) deep understanding of the ____ function

Addition

Select Sysdate,add_months (sysdate,12) from dual; --plus 1 
select Sysdate,add_months (sysdate,1) from dual;--plus 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-day 
Select Sysdate,to_char ( SYSDATE+1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --Add 1 hours 
to select Sysdate,to_char (sysdate+1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual;--plus 1 minutes 

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 

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 
- 

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 
---------- 

Similar:

Select To_char (sysdate, ' yyyy ') from dual; --year 
Select To_char (sysdate, ' Q ' from dual;--Quarterly 
Select To_char (sysdate, ' mm ') from dual;--month 

The first few days of the year of the DDD years in the year of WW years, weeks of the week 
of the week of the month 
in D-week, 
hh24 hours ( 
Mi) 

3: Take the current date is the week several Chinese display:

Sql> Select To_char (sysdate, ' Day ') from dual; 
To_char (sysdate, ' Day ') 
---------------------- 

4: If a table is indexed in a date-type field, how to use the

5: Get the current date

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 specific numeric value of the hour 
select Trunc (sysdate) + 1/24 from dual; 

7. Get the date of tomorrow 0:0 A.M. 0 seconds

Select Trunc (sysdate+1) from dual; 

8: Date of the month

9: Get the day of next month

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; 

11: Get every day of the year

Select Trunc (sysdate, ' yyyy ') + rn-1 date0 from 
(select RowNum rn from All_objects 

12: Today is the nth day of this year

13: How to add 2 years to the existing date

14: To determine whether a certain day is the year of the run year

15: Determine whether two years after the run year

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;

the Oracle time subtraction results in milliseconds, seconds, minutes, times, days,

Oracle Two-time subtraction default is days

Oracle Two-time subtraction default is the number of days *24 to the difference in hours

Oracle Two-time subtraction the default is the number of days *24*60 to the difference in minutes

Oracle Two-time subtraction the default is the number of days *24*60*60 to the difference in seconds

Give out the month of date2-date1

Sql> Select Months_between (' 1 September-December-1999 ', ' 1 September-March -1999 ') Mon_between from dual; 

Mon_between 
----------- 
  9 
sql>select months_between (to_date (' 2000.05.20 ', ' yyyy.mm.dd '), To_date (' 2005.05.20 ', ' yyyy.dd ') mon_betw from dual; 

MON_BETW 
--------- 

Oracle Calculation time Difference expression

--get two times of the difference Hao seconds select ceil (to_date (' 2008-05-02 00:00:00 ', ' yyyy-mm-dd hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' yy 
Yy-mm-dd hh24-mi-ss ')) * 24 * 60 * 60 * 1000) The difference of Hao seconds from DUAL; /* Difference hao seconds----------86401000 1 Row Selected * *-to get the difference in seconds between two times select Ceil (to_date (' 2008-05-02 00:00:00 ', ' yyyy-m 
M-DD hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' yyyy-mm-dd hh24-mi-ss ')) * 24 * 60 * 60) of the difference in seconds from DUAL; /* Difference seconds----------86401 1 Row Selected * *-to get the difference in minutes for two times Select Ceil ((to_date (' 2008-05-02 00:00:00 ', ' yyyy- 
MM-DD hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' yyyy-mm-dd Hh24-mi-ss '))) * 24 * 60) The difference in minutes from DUAL; /* The number of minutes----------1441 1 Row Selected * *-to get the difference between two hours select Ceil (to_date (' 2008-05-02 00:00:00 ', ' yyyy- 
MM-DD hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' yyyy-mm-dd hh24-mi-ss ')) * 24) The difference in hours from DUAL; /* The number of hours----------1 row selected */-Get the difference between two days select Ceil (to_date (' 2008-05-02 00:00:00 ', ' yyyy-m M-DD hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' yyyy-mm-dd hh24-mi-ss '))--the difference in number of days from DUAL;  /* Difference days----------2 1 Row Selected * *

Note: The number of days can be directly reduced by 2 dates, which is more convenient

--gets two-time month difference 
Select (EXTRACT from To_date (' 2009-05-01 ', ' yyyy-mm-dd '))-EXTRACT (' 2008-04-30 ') , ' Yyyy-mm-dd ')) 
       EXTRACT (Month from to_date (' 2008-05-01 ', ' yyyy-mm-dd '))-EXTRACT (Month from To_date (' 2008-04-30 ', ' yyyy-mm-dd ') months from 
dual; 
* 
MONTHS 
---------- 
1 row selected 

Note: You can use the Months_between function, more convenient

--Get two-time year difference 
select EXTRACT (' 2009-05-01 ', ' yyyy-mm-dd ')-EXTRACT (' 2008-04-30 '), ' Yyyy-mm-dd ') years from dual; 
/* 
YEARS 
---------- 
         

select sysdate,add_months (sysdate,12) from dual;   --plus 1 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;  --Add 1 days to select Sysdate,to_char (sysdate+1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual;  --Add 1 hours to select Sysdate,to_char (sysdate+1/24/60, ' Yyyy-mm-dd HH23:MI:SS ') from dual;  --Add 1 minutes to select Sysdate,to_char (sysdate+1/24/60/60, ' Yyyy-mm-dd HH23:MI:SS ') from dual;                     --Add 1 seconds to select sysdate+7 from dual; --plus 7 days