Sysdate () Simple usage

Environment:

CREATE TABLE RQ (XM VARCHAR2, age number,zw varchar (ten), Rzrq date);
INSERT into RQ values (' Little Cui ', 23, ' Ops Engineer ', (to_date (' 2017-07-02 ', ' yyyy-mm-dd '));
INSERT into RQ values (' Xiao Zhao ', $, ' Java Engineer ', (to_date (' 2013-04-12 ', ' yyyy-mm-dd '));
INSERT into RQ values (' small white ', 24, ' Network Engineer ', (to_date (' 2016-10-02 ', ' yyyy-mm-dd '));
INSERT into RQ values (' Little Tiger ', 43, ' Project manager ', (to_date (' 2003-09-15 ', ' yyyy-mm-dd '));
INSERT into RQ values (' leaflet ', 2, ' Technical Engineer ', (to_date (' 2009-07-02 ', ' yyyy-mm-dd '));
SELECT * FROM RQ

Date + number = date, which represents a date after several days.

Select A.*,rzrq +10 from RQ a

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

Date-Number = date, which represents a date several days ago.

Select A.*,rzrq-10 from RQ a

Date-date = number, which represents the number of days of the two-day period, but is definitely the big date-the small date.

Select A.*,trunc (SYSDATE-RZRQ) from RQ a

addition
Select A.*,add_months (rzrq,-12) from RQ A; --------plus 1 years
Select A.*,add_months (rzrq,-1) from RQ A; --Add January
Select A.*,to_char (rzrq-7, ' Yyyy-mm-dd HH24:MI:SS ') from RQ A;---plus 1 weeks
Select A.*,to_char (rzrq-1, ' Yyyy-mm-dd HH24:MI:SS ') from RQ A; --plus 1 days
Select A.*,to_char (rzrq-1/24, ' Yyyy-mm-dd HH24:MI:SS ') from RQ A; --plus 1 hours
Select A.*,to_char (rzrq-1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from RQ A; --plus 1 minutes
Select A.*,to_char (rzrq-1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from RQ A; --plus 1 seconds

Find out the month of employment for each employee until today

Select A.*,trunc (Months_between (SYSDATE,RZRQ)) from RQ a

Find out about employees who have been employed 5 years ago:

SELECT * FROM RQ where trunc (Months_between (SYSDATE,RZRQ))/5 >12

Note: In development, if it is a date function, it is recommended to use this statement to avoid the issue of leap years.

Get the current date and the first few weeks of the month:

Select ' Current system date: ' | | To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), ' the first of the Month ' | | To_char (Sysdate, ' W ') | | ' Week ' from RQ

Get the current date is the day of the week one:

Note: Sunday is the first day

Date is the day of the week Chinese display::

Select ' Current system date: ' | | To_char (sysdate), ' This Week ' | | To_char (sysdate, ' Day ') from RQ

Explain:

YYYY year

Q Quarter

MM Month

DD 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

To view the current system date:

Select A.*,sysdate from RQ a

Get date 0:0 A.M. 0 second of the day

Select Trunc (sysdate) from RQ;

Get the last second of the day

Select Trunc (sysdate) + 0.99999 from RQ;

Get the exact number of hours

Select Trunc (sysdate) + 1/24 from RQ;
Select Trunc (sysdate) + 7/24 from RQ;

Get the date for tomorrow 0:0 A.M. 0 seconds

Select Trunc (sysdate+1) from RQ;
Select Trunc (sysdate) +1 from RQ;

Get date of the month

Select Trunc (sysdate, ' mm ') from RQ;

Get the date of the next month

Select Trunc (Add_months (sysdate,1), ' mm ') from RQ;

Returns the last day of the current month

Select Last_day (sysdate) from RQ;

Select Last_day (trunc (sysdate)) from RQL;

Select Trunc (Last_day (sysdate)) from RQ;

Select Trunc (Add_months (sysdate,1), ' mm ')-1 from RQ;

Get every day of the year

Select Trunc (sysdate, ' yyyy ') + rn-1 date0 from (select RowNum rn from all_objects where rownum<366);

Get today is the nth day of the year

Select To_char (sysdate, ' DDD ') from dual;

How to add 2 years to an existing date

Select Add_months (sysdate,24) from dual;

Determine whether the year of the Day is a year to run

Select Decode (To_char (Last_day (trunc (sysdate, ' y ') +31), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;

Judging whether it is a year after two years

Select Decode (To_char (Last_day (Add_months (sysdate,24), ' y '), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;

Sysdate () Simple usage