msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Give Me The Current Date Please

Did you know that in addition to SYSDATE, there is also a SQL function called CURRENT_DATE? Basically, they are the same except one important difference.

SYSDATE returns the current date and time set for the operating system on which the database resides whereas CURRENT_DATE returns the current date in the session time zone.

Here is a quick example to illustrate that difference:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH:MI:SS'
  2  /

Session altered.

SQL> SELECT SYSDATE, CURRENT_DATE
  2    FROM DUAL
  3  /

SYSDATE             CURRENT_DATE
------------------- -------------------
04/17/2008 04:09:56 04/17/2008 04:09:57

SQL> ALTER SESSION SET TIME_ZONE = 'EST'
  2  /

Session altered.

SQL> SELECT SYSDATE, CURRENT_DATE
  2    FROM DUAL
  3  /

SYSDATE             CURRENT_DATE
------------------- -------------------
04/17/2008 04:09:56 04/17/2008 07:09:57 

Of course, I’m not telling you anything new here, it’s all in the documentation :)


Filed in Oracle on 17 Apr 08 | Tags:


Reader's Comments

  1. |

    why do people still store dates in anything but GMT… it’s beyond my imagination…

    Local dates cause ‘big issues’ when the apps are taken overseas!! Welcome to 2008 people! Local time and date – great for display, not for storage!!!

    (then you have no daylight saving issues for instance!!)

  2. |

    > then you have no daylight saving issues for instance some issues are related to Oracle and to the goverments changing rules over and over. Did you a know an island with about 500 people and 1000 birds did introduce a half-an-hour summer offset that produced bugs in 10gR2 :)

    select timestamp ‘2007-10-27 15:00:00 +00:00′ at time zone ‘Australia/LHI’ from dual; ORA-01878: specified field not found in datetime or interval

    But yes, using date instead of timestamp is probably not the best approach. Still a lot of function do not work with timestamp, like LAST_DAY, ADD_MONTHS and TRUNC

  3. |

    The should have called it CLIENT_DATE it’s the date where the Client operates :-)

    timestamps has another disadvanatge if you import (imp) data on a table with a timestamp, it’s not loded bulk but row per row. that’s what i experienced on 10.2;

    if a sysdate should be used or not could be depend on businesss rules. For example using ebay a sysdate should used to store your transaction to be able to compare it against other competitiors

    cheers Karl