关闭

SYSDATE and CURRENT_DATE in Oracle DB

标签: dateoracletimezoneinitializationfunctioncalendar
542人阅读 评论(0) 收藏 举报
分类:

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 datatype
DATE. 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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:47519次
    • 积分:811
    • 等级:
    • 排名:千里之外
    • 原创:26篇
    • 转载:38篇
    • 译文:3篇
    • 评论:3条
    最新评论