Oracle sysdate addition/subtraction code instance, oraclesysdate

Oracle sysdate addition/subtraction code instance, 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;