SYSDATE and CURRENT_DATE in Oracle DB

Term: SYSDATE

Definition:
In Oracle PL/SQL, SYSDATE is a pseudo column which always returns the operating system's current datetime value of DATE type. The format of theDATE output depends on the value ofNLS_DATE_FORMAT initialization parameter.

Note that:

1. The SYSDATE function requires no arguments.
2. You cannot use the SYSDATE function in the condition of a CHECK constraint.

Term: CURRENT_DATE

Definition:
The Oracle CURRENT_DATE function returns the current datein the session time zone, in a value in the Gregorian calendar of datatypeDATE. The format in which the date is displayed depends onNLS_DATE_FORMAT parameter. The default setting ofNLS_DATE_FORMAT isDD-MON-YY. This returns a 2-digit day, a three-character month abbreviation, and a 2-digit year.

 

/* Lastly, notice the behavior of CURRENT_DATE andSYSDATE Current_Date (new with 9i) is a lot likeSYSDATE but is timezone sensitive. */

SELECTTO_CHAR(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(sysdate, 'dd-mon-yyyy hh24:mi:ss')
FROMDUAL;

ALTER SESSION SET time_zone = local;

SELECTTO_CHAR(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(sysdate, 'dd-mon-yyyy hh24:mi:ss')
FROMDUAL;

 

 

SELECT dbtimezoneFROM dual;

--gives the timezone of the oracle server

SELECT sessiontimezoneFROM dual;

--gives the timezone of the oracle session.

 

SELECT * FROM V$TIMEZONE_NAMES

--returns timezone region name

 

select tz_offset( sessiontimezone ) from dual

--returns offset time for time zone region name.

 

References:

TIMESTAMP: http://psoug.org/reference/timestamp.html

SESSIONTIMEZONE : http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions143.htm

ALTER TIME ZONE: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2012.htm

V$TIMEZONE_NAMES: http://docs.oracle.com/cd/B12037_01/server.101/b10755/dynviews_2148.htm