Oracle Database SQL single-row functions-date functions

Oracle Database SQL single-row functions-date functions

Preface
The date functions of the Oracle database are very powerful and relatively large. I will sort it out for future reference.
. Convenience for yourself and convenience for others!
Common date functions
1. Sysdate: current date and time [SQL]
Select Sysdate from dual; 2. Last_day (): the last day of the month where a certain time is located [SQL]
Select Sysdate from dual;
3. Add_moths (d, n): the current date d is pushed for n months. It is generally used to increase or decrease the number of months from a date value. [SQL]
Select Add_months (Sysdate, 12) from dual; -- increase by one year
Select Add_months (Sysdate,-12) from dual; -- Reduce by 12 months.
4. Months_between (f, s): The number of months between the date f and s [SQL]
Select months_between (sysdate, to_date ('1970-11-12 ', 'yyyy-mm-dd') from dual;

The usage of to_date () will be introduced later
5. Next_day (d, day_of_week): return the date after the specified date in the variable d, which is named day_of_week.
The date of the business day. (Day_of_week must be one day of the week) [SQL]
Select next_day (sysdate, 3) from dual; check the result:

 
Why is this result? Because 1 represents Sunday, and 3 represents week 2.
The system time has passed Tuesday, so it will represent Tuesday next week.
6. Current_date (): returns the current date in the current session time zone [SQL]
Alter session set time_zone = '-11: 00 ';
Select sessiontimezone, current_timestamp from dual; 7. dbtimezone (): return time zone [SQL]
Select dbtimezone from dual; 8. extract (): locate the field value of the date or interval value [SQL]
-- Interval value of the month
Select extract (month from sysdate) "This Month" from dual;
-- Daily interval value
Select extract (day from sysdate) "This Month" from dual; 9, localtimestamp (): returns the date and time in the session [SQL]
Select localtimestamp from dual;

10. TRUNC (for dates): The date value truncated for the specified element.
The syntax is as follows:
TRUNC (date [, fmt])
Date: date Value
Fmt: date format, which is truncated by the specified Element format. ignore its
Then, it is intercepted by the latest date.
Let's take a look at the specific usage:
(1) Cut the end by year [SQL]
Select TRUNC (TO_DATE ('2017-04-05 ', 'yyyy-mm-dd hh: mi'), 'yyyy ')
From dual;

(2) Cut the end of a month [SQL]
Select TRUNC (TO_DATE ('2017-04-05 ', 'yyyy-mm-dd hh: mi'), 'mm ')
From dual;
As for the rest, I will not demonstrate how to cut the end on time by day.

11. INTERVAL: + 1 [SQL] at the current time
Select * from emp where hiredate> to_date ('2017/7', 'yyyy/MM/dd ')
+ Interval '1' year;
-- Find all information about employees whose employment time is later

Common date data processing functions
The above uses to_date (), which is a function for processing the date data format. There is also a to_char () function, the two
The function is used to process the date format. As for its specific usage, I do not want to describe it too much here, so I will have the opportunity to proceed later.
Summary.