Home > SQL, Technical > Date functions in Oracle SQL

Date functions in Oracle SQL

July 26, 2012

Function 1: sysdate

Purpose : Returns the system sate

Syntax: sysdate

Example : SELECT sysdate FROM DUAL;

Output:  26-JUL-12

———————————————————————————————

Function 2: ROUND

Purpose : Round to the nearest date

Syntax: Round(date,<format>)

Example : SELECT round(sysdate,’MM’) FROM DUAL;

Output:  01-AUG-12

———————————————————————————————

Function 3: TRUNC

Purpose : Truncate down the date to the nearest date

Syntax: TRUNC(date,<format>)

Example : SELECT TRUNC(sysdate,’MM’) FROM DUAL;

Output:  01-JUL-12

———————————————————————————————

Function 4: NEXT_DAY

Purpose : Returns the next date when that day falls off

Syntax: NEXT_DAY(date,day)

Example : SELECT NEXT_DAY(sysdate,’SUNDAY’) FROM DUAL;

Output:  29-JUL-12

———————————————————————————————

Function 5: LAST_DAY

Purpose : Returns the last date of the month.

Syntax: LAST_DAY(date)

Example : SELECT LAST_DAY(sysdate) FROM DUAL;

Output:  31-JUL-12

———————————————————————————————

Function 6: ADD_MONTHS

Purpose : add n months to given date.

Syntax: add_months(date,n)

Example : SELECT add_months(sysdate,2) FROM DUAL;

Output:  26-SEP-12

———————————————————————————————

Function 7: MONTHS_BETWEEN

Purpose : returns the number of months in between two dates.

Syntax: Months_between(date1,date2)

Example : SELECT months_between(sysdate,sysdate+40) FROM DUAL;

Output:  -1.29

———————————————————————————————

Function 8: NUMTODSINTERVAL

Purpose : returns interval_unit amount of time(hour,minute and second)

Syntax:numtodsinterval(n,interval_unix)

Example : SELECT numtodsinterval(30,’HOUR’) FROM DUAL;

Output: 1 6:0:0.0 (1 day and 6 hours)

———————————————————————————————

Function 9: NUMTOYMINTERVAL

Purpose : returns interval_unit amount of time(year and month)

Syntax:numtoyminterval(n,interval_unix)

Example : SELECT numtoyminterval(30,’MONTH’) FROM DUAL;

Output: 2-6(means 2 years 6 months)

Advertisements
Categories: SQL, Technical
%d bloggers like this: