Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  Oracle date functions


Oracle (var)char functions
Instr function
Number format
Kill oracle session
to_date function
Oracle sysdate
Oracle substr
How to use the DECODE statement
How to use the CASE statement
How to use the NVL statement
Using XML functions
Oracle date format
Oracle numeric functions
Oracle date functions
Pl sql trim


Oracle date functions

Oracle has a number of functions that apply to a date
Sysdate Returns the current date/time
ADD_MONTHS Function to add a number of months to a date. For example: add_months(SYSDATE,3) returns 3 months after sysdate. This could be rounded to below is the resulting month has fewer days than the month this function is applied to.
+,- (plus/minus) In Oracle you can add or substract a number of days from a date. Example: sysdate+5 means systemdate/time plus 5 days
GREATEST With the greatest function you can select the date/time that is the highest in a range of date/times. Example: greatest (sysdate+4,sysdate,sysdate-5) = sysdate+4.
LEAST With the least function you can select the earliest date/time in a range of date/times. Example: least(sysdate+4,sysdate,sysdate-5) = sysdate-5.
LAST_DAY Returns the last_day of a month based on the month the passed date is in. Example: last_day(sysdate) returns the last day of this month.
MONTHS_BETWEEN Returns the number of months between two dates. The number is not rounded. Example: months_between(sysdate, to_date('01-01-2007','dd-mm-yyyy')) returns the number of months since jan 1, 2007.
NEXT_DAY Date of next specified date following a date NEXT_DAY(, ) Options are SUN, MON, TUE, WED, THU, FRI, and SAT SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual; NOTE: This can be dependend on NLS_SETTINGS!
ROUND Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day ROUND(, ) SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR FROM dual;
TRUNC Convert a date to the date without time (0:00h) Example: TRUNC(sysdate) returns today without time.
First day of the month. trunc(example_date,'MM') Example: select trunc(TO_DATE('31-JAN-2007'),'MM') FROM dual;
TO_CHAR(date,format_mask) Converts a date to a string using a format mask. Format masks are explained here