Oracle Foundation (13) Date function

Date function

Sysdate

-- Current system Time Select  from dual;

EXTRACT

-- get Current year Select Extract (year from from-- years Select Extract (  month from from– month Select Extract (Day  from- - Day

To_date

--Convert a string to a dateSELECTTo_date ('2014-12-31','YYYY-MM-DD') fromDUAL;SELECTTo_date ('2014-12-31 15:20:45','YYYY-MM-DD Hh24:mi:ss') fromDUAL;SELECTTo_date ('December 31, 2014','yyyy "Year" MM "month" DD "Day') asT fromDUAL;--number of days between two datesSELECT  Floor(sysdate-To_date ('20141201','YYYYMMDD')) fromDUAL;

To_date format (time: 2007-11-02 13:45:25 for example)

Category	Format	Description	Value
Year	Yy	Two digits two-year	07
	yyy	Three digits three-year	007
	yyyy	Four digits four-year	2007
Month	Mm	Number two-month	11
	Mon	Abbreviated Character Set representation	November, if the English version shows the Nov
	Month	Spelled out character Set representation	If English version, show November
Day:	Dd	Number of days in the month	02
	Ddd	Number of days of the year	02
	Dy	Abbreviated when in for a few days	Friday, if the English version, show Fri
	Day	Spelled out when in a few days all written	Friday, if the English version, show Friday
Hour:	hh	12-hour binary digits	01
	Hh24	24-hour binary digits	13
Minute	Mi	Digits 60 binary	45
Second	Ss	Digits 60 binary	25
Other:	Q	Digit Quarter	4
	Ww	Digit The first few weeks of the year	44
	W	Digit Week of the month	1

To_char

SELECTTo_char (Sysdate,'YYYY-MM-DD Hh24:mi:ss') asNowtime fromDUAL;--Date converted to stringSELECTTo_char (Sysdate,'yyyy') asNowyear fromDUAL;--get the year of the timeSELECTTo_char (Sysdate,'mm') asNowmonth fromDUAL;--get the month of the timeSELECTTo_char (Sysdate,'DD') asNowday fromDUAL;--get the day of the timeSELECTTo_char (Sysdate,'hh24') asNowhour fromDUAL;--when you get the timeSELECTTo_char (Sysdate,'mi') asNowminute fromDUAL;--get the minutes of the timeSELECTTo_char (Sysdate,'SS') asNowsecond fromDUAL;--gets the seconds of the timeSELECTTo_char (Sysdate,'yyyy "Year" MM "month" DD "Day') asT fromDUAL;--time format with Chinese

Months_between (DATE1,DATE2)

-- calculates the month difference between two dates Select months_between ('January-January -2014'3 January-December -2014'  from dual; Results: - 11.9677419354839

Add_months (Date,month)

--month added, 3 months after 2014-5-20SelectAdd_months ('20月-May on -2014',3) fromdual; Results: the/8/ ---month added, 3 months before 2014-5-20SelectAdd_months ('20月-May on -2014',-3) fromdual; Results: the/2/ -

Next_day (Date,week)

-- returns the week after the specified date, and returns the number of the next Saturday after today Select ' Saturday '  from dual;

Last_day

-- returns the last day of the month on which the specified date is Select  from dual;

ROUND

--rounding a date in the specified formatSelect round(Sysdate,' Year') fromDual--by yearSelect round(Sysdate,'Month') fromDual--by MonthSelect round(To_date ('2014-11-16','YYYY-MM-DD'),' Day') fromDual--according to the weekSelect round(To_date ('2014-11-16','YYYY-MM-DD'),'DD') fromDual--by Date

Oracle Foundation (13) Date function