For more information about the use of ORACLE time functions (SYSDATE), oraclesysdate

For more information about the use of ORACLE time functions (SYSDATE), oraclesysdate

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 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 ------------------- 20030327 4 18:16:09 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; -- select to_char (sysdate, 'q' from dual; -- select to_char (sysdate, 'mm') from dual; -- month select to_char (sysdate, 'dd') from dual; -- day

The day in the year of ddd. The day in the year of WW. W. The day in the week of the month. hh hour (12) hh24 hour (24) Mi minute ss second

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; -- get the specific value of the hour 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;

Oracle time minus milliseconds, seconds, minutes, hours, and days,

Oracle two time minus the default is the number of days

Oracle two time subtraction default is the number of days * 24 is the difference of hours

Oracle two-time subtraction default is the number of days * 24*60 is the number of minutes of difference

Oracle two time subtraction default is the number of days * 24*60*60 is the difference in seconds

--MONTHS_BETWEEN(date2,date1) 

Given the month of the date2-date1

SQL> select months_between ('19-December-1999 ', '19-March-1999') mon_between from dual; MON_BETWEEN ----------- 9 SQL> select months_between (to_date ('2000. 05.20 ', 'yyyy. mm. dd'), to_date ('2017. 05.20 ', 'yyyy. dd') mon_betw from dual; MON_BETW ----------60

Oracle Computing Time Difference Expression

-- Get the difference between two time grand seconds select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ') -To_date ('1970-04-30 23:59:59 ', 'yyyy-mm-dd hh24-mi-ss') * 24*60*60*2008) differs from dual in seconds; /* number of seconds with different Hao ---------- 86401000 1 row selected */-- obtain the number of seconds with different time select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('2017-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') * 24*60*60) difference in seconds from dual;/* difference in seconds ---------- 86401 1 row selected */-- get the difference in minutes between two times select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('2017-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') * 24*60) difference minutes from dual;/* difference minutes ---------- 1441 1 row selected */-- get the difference hours of two time select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('2017-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') * 24) hours of variation from dual; /* hours of difference ---------- 25 1 row selected */-- obtain the number of different days of the two time select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss ')-To_date ('1970-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss ') days from dual; /* Days of difference ---------- 2 1 row selected */

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

-- Obtain the two-time month difference select (EXTRACT (year FROM to_date ('2017-05-01 ', 'yyyy-mm-dd ')) -EXTRACT (year FROM to_date ('1970-04-30 ', 'yyyy-mm-dd') * 12 + EXTRACT (month FROM to_date ('1970-05-01 ', 'yyyy-mm-dd')-EXTRACT (month FROM to_date ('2017-04-30 ', 'yyyy-mm-dd') months from dual; /* MONTHS ---------- 13 1 row selected */

Note: You can use the months_between function for greater convenience.

-- Obtain the two-time year difference select EXTRACT (year FROM to_date ('1970-05-01 ', 'yyyy-mm-dd ')) -EXTRACT (year FROM to_date ('1970-04-30 ', 'yyyy-mm-dd') years from dual;/* YEARS ---------- 1

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 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 HH23: MI: ss') from dual; -- add 1 minute select sysdate, TO_CHAR (sysdate + 1/24/60/60, 'yyyy-mm-dd HH23: MI: ss') from dual; -- add 1 second select sysdate + 7 from dual; -- add 7 days