Date functions for common functions of Oracle

---------------------------------------------Date/Time function-----------------------------------------------
--1:sysdate
The current date used to get the system

SELECT sysdate from DUAL;

--2:add_months
Add or subtract a month

SELECT To_char (add_months (to_date (' 20080818 ', ' YYYYMMDD '), 2), ' Yyyy-mm-dd ') from DUAL;

SELECT To_char (Add_months (Sysdate,-1), ' Yyyy-mm-dd ') from DUAL

--3:last_day
Returns the last day of the date

SELECT Last_day (sysdate) from DUAL;

SELECT Last_day (Add_months (Sysdate,-2)) from DUAL;

--4:months_between (DATE2,DATE1)
Give the month of date2-date1

--The format of the parameter requires attention

SELECT Months_between (to_date (' 2011-05-03 ', ' yyyy-mm-dd '), to_date (' 2011-01-23 ', ' Yyyy-mm-dd ')) from DUAL;

SELECT Months_between (' 1 September-December-1999 ', ' 1 September-March -1999 ') Mon_between from DUAL;

--select months_between (' 20 November-January-23 ', ' 20 November-September-1 ') from DUAL; The text does not match the format string

--5:new_time (date, ' This ', ' that ')
Give the date and time of the =other time zone in this time zone

SELECT to_char (sysdate, ' YYYY. Mm. DD HH24:MI:SS ') Beijing_time,
To_char (New_time (sysdate, ' PDT ', ' GMT '), ' YYYY. Mm. DD HH24:MI:SS ') los_angels from DUAL;


Abbreviated Time zone
AST OR ADT Atlantic Standard Time
HST OR HDT Alaska-Hawaii Time
BST OR BDT UK Daylight Time
MST OR MDT US Mountain Time
CST OR CDT US Central Time zone
NST Newland Standard Time
EST OR EDT US Eastern Time
PST OR PDT Pacific Standard Time
GMT Grenwy Standard Time
YST OR YDT Yukon Standard Time

--6:next_day
Return the exact date of the Sunday number that you requested in the same one-week or one-week period after the date of enactment.


--Sunday = 1 week one = 2 weeks two = 3 weeks three = 4 weeks four = 5 weeks five = 6 weeks six = 7

SELECT Next_day (Sysdate, 2) from DUAL;


--7:current_date
Current date in the current session time zone

SELECT current_date from DUAL;

--8:current_timestamp
Returns the current date in the current session timezone with the timestamp with time zone data type

SELECT Current_timestamp from DUAL;

--9:dbtimezone ()
Return time zone

SELECT Dbtimezone from DUAL;

--10:sessiontimezone
Return to the time zone where Dbtimezone is a database and the session is for the current session because the time zone can be changed at the session level

SELECT Sessiontimezone from DUAL;

ALTER SESSION SET time_zone = ' 8:00 ';

SELECT Sessiontimezone from DUAL;

--11:extract
Find field values for date or interval values

Select EXTRACT (month from sysdate) ' month ' from DUAL;

Select EXTRACT (Day from Sysdate) as ' Day ' from DUAL;

Select EXTRACT (year from sysdate) as ' year ' from DUAL;

--12:localtimestamp
Returns the date and time in a session

SELECT Localtimestamp from DUAL;

The--13:trunc (for dates) TRUNC function truncates the date value for the specified element. Its specific syntax format is as follows: TRUNC (Date[,fmt]) Where: Date a datetime value FMT date format, the date will be truncated by the specified element format. Ignoring it is truncated by the most recent date: TRUNC (to_date (' 24-nov-1999 "), ' dd-mon-yyyy hh:mi am ') = ' 24-nov-1999 12:00:00 am ' TRU NC (To_date (' 24-nov-1999 08:37 pm ', ' dd-mon-yyyy hh:mi am '), ' hh ') = ' 24-nov-1999 08:00:00 am ' SELECT TRUNC (sysdate, ' yyyy ') From DUAL;　 -Return to the first day of the year. SELECT TRUNC (sysdate, ' MM ') from DUAL;　　-Returns the first day of the month. SELECT TRUNC (sysdate, ' D ') from DUAL;　　--Returns the first day of the current week. SELECT TRUNC (sysdate, ' DD ') from dual;--returns the current month day
----Last day of last month

SELECT To_char (Last_day (Add_months (Sysdate,-1)), ' Yyyy/mm/dd ') from DUAL;

----: The previous month's Today
SELECT To_char (Add_months (Sysdate,-1), ' Yyyy-mm-dd ') from DUAL;

----The first day of last month

SELECT To_char (Add_months (Last_day (sysdate) +1,-2), ' Yyyy-mm-dd ') firstday from DUAL;

---by weekly statistics

SELECT to_char (sysdate, ' WW ') from DUAL;

---by monthly statistics

SELECT to_char (sysdate, ' MM ') from DUAL;

----Statistics per quarter

SELECT to_char (sysdate, ' Q ') from DUAL;

---According to yearly statistics

SELECT to_char (sysdate, ' YYYY ') from DUAL;

---to find the exact date of all Friday in a month

SELECT to_char (T.D, ' yy-mm-dd ')
From
(
SELECT TRUNC (sysdate, ' MM ') + ROWNUM-1 as D
From Dba_objects
WHERE ROWNUM < 32
) T
WHERE to_char (T.D, ' mm ') = To_char (sysdate, ' mm ')
and TRIM (To_char (T.D, ' Day ')) = ' Friday '

The following should belong to the Format function, but in order to give a detailed description of the date function, we put the contents of To_char here.

Y or yy or yyy last, two-bit or three-bit
SELECT to_char (sysdate, ' YYY ') from DUAL;
SELECT to_char (sysdate, ' YY ') from DUAL;

Syear or year Syear adds a minus sign to the BC
SELECT to_char (sysdate, ' syear ') from DUAL; --twenty Eleven

Q quarter, January-March for the first quarter
SELECT to_char (sysdate, ' Q ') from DUAL; --2 indicates the second quarter

MM number of months
SELECT to_char (sysdate, ' MM ') from DUAL; --04 says April

The Roman representation of the RM month
SELECT to_char (sysdate, ' RM ') from DUAL; --iv says April

Month name in month with 9 character length
SELECT to_char (sysdate, ' MONTH ') from DUAL; -April

The first few weeks of WW year
SELECT to_char (sysdate, ' WW ') from DUAL; --24 means the 24th week of June 13, 2002

W this month the first few weeks
SELECT to_char (sysdate, ' W ') from DUAL; -April 26, 2011 is the 4th week

The first few days of DDD year. January 1 is 001, February 1 is 032
SELECT to_char (sysdate, ' DDD ') from DUAL;

DD Day of the month
SELECT to_char (sysdate, ' DD ') from DUAL;

Days within D week
SELECT to_char (sysdate, ' D ') from DUAL;

DY week abbreviation for the first day
SELECT to_char (sysdate, ' DY ') from DUAL;

HH or HH12 12 hours of input
SELECT to_char (sysdate, ' HH ') from DUAL;

HH24 24-hour system
SELECT to_char (sysdate, ' HH24 ') from DUAL;

MI minutes (0~59)
SELECT to_char (sysdate, ' MI ') from DUAL;
Note Do not use mm format for minutes (MI should be used in minutes). MM is the format used for the month, and it works for minutes, but the result is wrong.

SS Number of seconds (0~59)
SELECT to_char (sysdate, ' SS ') from DUAL;

--------------------------------------------------------------------------------------

Date functions for common functions of Oracle