SQL Pocket Guide, 3rd Edition

Datetime Functions: Oracle

Oracle implements a wide variety of helpful functions for working with dates and times.

Getting Current Date and Time

It is common to invoke SYSDATE to return the current date and time in the server’s time zone. For example:

SELECT SYSDATE FROM dual;

2006-02-07 09:32:32

You can use ALTER SESSION to specify a default date format for your session using the date format elements described in Table 1-2.

ALTER SESSION
   SET NLS_DATE_FORMAT = 'dd-Mon-yyyy hh: mi:ss';

The following Oracle functions return current datetime information:

CURRENT_DATE

Returns the current date in the session time zone as a value of type DATE.

CURRENT_TIMESTAMP[(precision)]

Returns the current date and time in the session time zone as a value of type TIMESTAMP WITH TIME ZONE. The precision is the number of decimal digits used to express fractional seconds; it defaults to 6.

LOCALTIMESTAMP[(precision)]

The same as CURRENT_TIMESTAMP, but it returns a TIMESTAMP value with no time zone offset.

SYSDATE

Returns the server date and time as a DATE.

SYSTIMESTAMP[(precision)]

Returns the current server date and time as a TIMESTAMP WITH TIME ZONE value.

DBTIMEZONE

Returns the database server time zone as an offset from UTC in the form '[+|-]hh:mi'.

SESSIONTIMEZONE

Returns the session time zone as an offset from UTC in the form '[+|-]hh:mi'.

Rounding and Truncating

Oracle allows you to round and truncate DATE values to specific datetime elements. The following example illustrates rounding and truncating to the nearest ...