Thursday, May 7, 2009

Oracle date functions

Here is a list of the Oracle Date functions. I had enough problems with the Oracle dates in the past, so I created this list, maybe someone will find it useful.

Oracle SYSDATE function

Returns the current date and time set for the operating system on which the database resides.

Syntax: SYSDATE
Example:

SELECT SYSDATE FROM dual;


Oracle CURRENT_DATE function

Returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.

Syntax: CURRENT_DATE
Example:

SELECT CURRENT_DATE FROM dual;


Oracle "+" and "-" (plus/minus) date operators

Add or substract a number of days from a date.

Syntax: {date} + {integer}
{date} - {integer}
Example:

SELECT SYSDATE + 1 FROM dual;

SELECT SYSDATE - 1 FROM dual;


Oracle ADD_MONTHS function

Function to add a number of months to a date. This could be rounded to below if the resulting month has fewer days than the month this function is applied to.

Syntax: ADD_MONTHS
Example:

SELECT add_months(SYSDATE, 2) FROM dual;


Oracle DUMP function

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value.

Syntax: DUMP({value} [,{return_format}[,{start_position}[,{length}]]]){value}{return_format}{start_position}{length}{value}{return_format}{start_position}{length}
Example:

SELECT add_months(SYSDATE, 2) FROM dual;


Oracle GREATEST function

With the greatest function you can select the date/time that is the highest in a range of date/times.

Syntax: GREATEST
Example:

SELECT GREATEST(SYSDATE-7, SYSDATE-9, SYSDATE+2)FROM t;


Oracle INTERVAL function

Interval to adjust date-time.

Syntax: INTERVAL '{integer}' {unit}
Example:

SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS') FROM dual;


Oracle LEAST function

With the least function you can select the earliest date/time in a range of date/times.

Syntax: LEAST({date}, {date}, {date}, ...)
Example:

SELECT LEAST(datecol1, datecol2, datecol3) FROM t;


Oracle LAST_DAY function

Returns the last_day of a month based on the month the passed date is in.

Syntax: LAST_DAY({date})
Example:

SELECT LAST_DAY(CURRENT_DATE) FROM t;


Oracle LEAST function

With the least function you can select the earliest date/time in a range of date/times.

Syntax: LEAST({date}, {date}, {date}, ...)
Example:

SELECT LEAST(datecol1, datecol2, datecol3) FROM t;


Oracle MONTHS_BETWEEN function

Returns the number of months between two dates. The number is not rounded.

Syntax: MONTHS_BETWEEN({latest_date}, {earliest_date})
Example:

SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;


Oracle NEXT_DAY function

Date of next specified date following a date NEXT_DAY(, ) Options are SUN, MON, TUE, WED, THU, FRI, and SAT. This can be dependend on NLS_SETTINGS!

Syntax: NEXT_DAY({date}, {day of the week})
Example:

SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual;


Oracle ROUND function

Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day.

Syntax: ROUND({date_value}, {format})
Example:

SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR FROM dual;


Oracle TRUNC function

Convert a date to the date without time (0:00h).

Syntax: TRUNC({date_time})
Example:

SELECT TRUNC(SYSDATE);


3 comments:

  1. Hi,

    How would I find the date of the previous Sunday before today?

    thanks!

    ReplyDelete
  2. Renvoi toujours le dimanche précédent même si la date du jour (aujourd'hui) est un dimanche.

    select to_char((sysdate - to_number(to_char(sysdate,'D'))),'DAY dd/MM/yyyy') from dual ;

    Renvoi le dimanche précédent sauf si la date du jour (aujourd'hui) est un dimanche alors il renvoi la date du jour.

    select
    case
    when to_number(to_char(sysdate,'D')) = 7 then to_char(sysdate,'DAY dd/MM/yyyy')
    else to_char(
    (sysdate
    -
    to_number(to_char(sysdate,'D'))
    ),'DAY dd/MM/yyyy'
    )
    end
    from dual;

    ReplyDelete
  3. you have a nice site. thanks for sharing this valuable resources. keep it up. anyway, various kinds of ebooks are available here

    http://feboook.blogspot.com

    ReplyDelete