SYSDATE是我们在Oracle开发中经常要用到的一种单行函数(single row function),该函数用以返回当前的日期与时间，常和DUAL伪表一起合作。SYSDATE实际上指的是SYSDATE()函数，但是因为这个函数没有参量，所以这里可以省略()圆括号。另外SYSDATE函数是用于获取数据库所在的操作系统的当前时间值的，所以它和数据库或会话的时区没有关系。 我们可以使用NLS_DATE_FORMAT参数或者TO_CHAR函数来获得我们想要的SYSDATE日期格式，具体的格式代码如下：
具体的用法如：
| Format Code | Explanation |
|---|---|
| YEAR | Year, spelled out |
| YYYY | 4-digit year |
| MM | Month (01-12; JAN = 01). |
| MON | Abbreviated name of month. |
| MONTH | Name of month, padded with blanks to length of 9 characters. |
| D | Day of week (1-7). |
| DAY | Name of day. |
| DD | Day of month (1-31). |
| DDD | Day of year (1-366). |
| DY | Abbreviated name of day. |
| HH | Hour of day (1-12). |
| HH12 | Hour of day (1-12). |
| HH24 | Hour of day (0-23). |
| MI | Minute (0-59). |
| SS | Second (0-59). |
| SSSSS | Seconds past midnight (0-86399). |
syntax:select to_char(sysdate,'FORMAT CODES') from dual;select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual;TO_CHAR(SYSDATE,'YEAR-MON-DAY HH24:MI:SS')--------------------------------------------------------------------------------TWENTY ELEVEN-AUG-WEDNESDAY 21:34:43与SYSDATE不同，CURRENT_DATE()函数会返回数据库会话所设置的本地时区的当前日期。 我们来具体看一下这2个函数所返回结果的差异:
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE 11.2.0.2.0 ProductionTNS for Linux: Version 11.2.0.2.0 - ProductionNLSRTL Version 11.2.0.2.0 - ProductionSQL> select * from global_name;GLOBAL_NAME--------------------------------------------------------------------------------www.oracledatabase12g.comSQL> alter session set nls_date_format='YYYY-MM-DD HH24:mi:ss';Session altered.SQL> set linesize 200 pagesize 1400;SQL> col sessiontimezone for a20SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual;CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE------------------- ------------------- ------ --------------------2011-08-24 19:53:09 2011-08-24 19:53:09 +08:00 +08:00/* 我们调整session的所在时区到+09:00 */SQL> alter session set time_zone='+09:00';Session altered.SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual;CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE------------------- ------------------- ------ --------------------2011-08-24 20:56:33 2011-08-24 19:56:33 +08:00 +09:00/* 可以看到current_date所返回的时间增长了一个小时，而SYSDATE不变 */另外请注意SYSDATE函数的类型并非是DATE类型，而是其特有的类型:
SQL> create table mytime(t1 date);Table created.SQL> insert into mytime values (sysdate);1 row created.SQL> commit;Commit complete.SQL> select dump(t1), dump(sysdate), dump( current_date) from mytime;DUMP(T1)--------------------------------------------------------------------------------DUMP(SYSDATE)--------------------------------------------------------------------------------DUMP(CURRENT_DATE)--------------------------------------------------------------------------------Typ=12 Len=7: 120,111,8,24,21,31,59Typ=13 Len=8: 219,7,8,24,20,32,12,0Typ=13 Len=8: 219,7,8,24,20,32,12,0以上可以看到sysdate和current_date都属于TYPE=13，而普通DATE类型为TYPE=12数据类型type=12代表DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD. 因为sysdate的时钟取自操作系统，所以DBA同样需要关心操作系统本身时区的设置。不当的时区设置可能导致数据库内部的定时作业系统紊乱，主要会影响使用DBMS_JOB调用的一些JOB，DBMS_JOB包不会考虑到时区的因素，而仅仅考虑sydate的值(DBMS_JOB uses the "date" datatype to store the start date/time and does not store/use timezone related information. JOBS who are scheduled are always executed when sysdate => next execution time. )。如果使用DBMS_SCHEDULER则会考虑具体的时区。 此外一些使用夏令时的地区还会面临更复杂的DST问题，详细可以参考ORAganism的文章 和MOS文档 & :
DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained. [ID 467722.1]Applies to:Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.2.0.2 - Release: 9.0.1 to 11.2Information in this document applies to any platform.PurposeThis note gives a overview of how DBMS_SCHEDULER / DBMS_JOB react on DST transitions and whatto know when using timezone information with jobs.Scope and ApplicationTo be used when DBMS_SCHEDULER jobs run at a wrong time after a DST change or you have outputfrom jobs selecting from TIMESTAMP WITH LOCAL TIMEZONE columns that is not correct.If things about timezones are not clear please have first a look at Note 340512.1 Timestamps& time zones - Frequently Asked Questions.DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained.When running trough DBMS_SCHEDULER or DBMS_JOB a job output using TIMESTAMP WITH LOCALTIMEZONE gives the wrong time.This happens when you use DBMS_SCHEDULER or DBMS_JOB to run jobswho select information from TIMESTAMP WITH LOCAL TIMEZONE fields.The time in a TIMESTAMP WITH LOCAL TIMEZONE that will be returned depends on the SESSIONTIMEZONE.select sessiontimezone from dual;If a TIMESTAMP WITH LOCAL TIMEZONE field contains for example "20-DEC-2007 15:15 -08:00" andyour session timezone is also -08:00 you will see 15:15.If you change the session timezone to (for example) -07:00 then you will see 16:15,witch is normal and intended.Jobs using DBMS_SCHEDULER and DBMS_JOB however use a SESSIONTIMEZONE set to UTC (= +00:00 ).Hence, above example will give 15 + 8 = 23:15 when selected trough a job.If you use jobs that provide TIMESTAMP WITH LOCAL TIMEZONE then you may need toincluded in the job an alter session to change the SESSIONTIMEZONE.execute immediate 'alter session set time_zone = ''-08:00''';If you want the job to follow DST rules then you need to use a named timezone thatfollows the DST rules you want to use:execute immediate 'alter session set time_zone = ''US/Pacific''';There is Enhancement Request 5479680 - INCORPORATE SESSIONTIMEZONE INFORMATION INTOJOB INFORMATION to a) use the session timezone of the submitting session as timezone and b)have a way to see with what timezone a job is submitted.Job runtime time after DST has changed.When using DBMS_JOB:DBMS_JOB uses the "date" datatype to store the start date/time and does not store/use timezone