ORACLE Date function

ORACLE Date function
Sysdate the current database system time
Add_months (plus minus the specified month)
Months_between (number of months separated by two dates)
Last_day (the last day of the month on which the specified date is taken)

Example statements:
Time to fetch the current database system
SELECT sysdate from DUAL;

Display in a specific time format
SELECT to_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS ') from DUAL;

Change the current session to set the current session date display format uniformly
ALTER SESSION SET nls_date_format= ' yyyy-mm-dd HH24:MI:SS ';

Shows the time after one months
SELECT add_months (sysdate,1) from DUAL;

Show time in previous one months
SELECT add_months (sysdate,-1) from DUAL;

Displays the number of months between the last one months and the first one months
SELECT Months_between (Add_months (sysdate,1), Add_months (sysdate,-1)) from DUAL;

Show the time of the last day of the month
SELECT Last_day (sysdate) from DUAL;

ROUND (DATE, ' format_string)
Function: Rounds a given date by a date mask format

SELECT ROUND (sysdate, ' YYYY '), ROUND (sysdate, ' MM '), ROUND (sysdate, ' DD ') from DUAL;

TRUNC (DATE, ' format_string)
Function: intercepts a given date by the date mask format
SELECT TRUNC (sysdate, ' YYYY '), TRUNC (sysdate, ' MM '), TRUNC (sysdate, ' DD ') from DUAL;

To_char (date, ' format_string ') (datetime data is converted to character data by the format requirement of the time mask format_string)
To_date (' string ', ' format_string ') (the character data string is converted to the corresponding date type according to the format requirement of the time mask format_string)

mask element
yyyy	four-digit year (for example: 2005)
yy	two-digit year (e.g.  05
q	quarter (1-4
mm	month (01-12
WW	1-53
w	1-5
DDD	1-366
DD	1-31
d	1-71 Saturday for 7
hh24	24 hour system (
mi	minutes (0-59
ss	seconds (0-59
Sssss	Seconds from After midnight (0-86399)

View the current date's year, month, date, hour, minute
SELECT to_char (sysdate, ' YYYY ') Year,to_char (sysdate, ' MM ') Month,to_char (sysdate, ' DD ') Day,
To_char (sysdate, ' HH24 ') Hour,to_char (sysdate, ' MI ') Minute,to_char (sysdate, ' SS ') S
From DUAL;

View the current date is the quarter ordinal of the year
SELECT to_char (sysdate, ' Q ') from DUAL;

View the current date is the first few weeks of the year, in the week ordinal of the month
SELECT to_char (sysdate, ' WW '), To_char (sysdate, ' W ') from DUAL;

View the current date is the day of the year in the first day of the week (Sunday is 1, Saturday is 7)
SELECT to_char (sysdate, ' DDD '), To_char (sysdate, ' D ') from DUAL;

Http://www.cnblogs.com/jinzhili/articles/1115725.html

ORACLE Date function