O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Getting the Current Date and Time

In any language, it’s important to know how to get the current date and time. How to do that is often one of the first questions to come up, especially in applications that involve dates in any way, as most applications do.

Up through Oracle8i Database, you had one choice for getting the date and time in PL/SQL: you used the SYSDATE function, and that was it. Beginning with Oracle9i Database, you have all the functions in Table 10-1 at your disposal, and you need to understand how they work and what your choices are.

Table 10-1. Comparison of functions that return current date and time

Function

Time zone

Datatype returned

CURRENT_DATE

Session

DATE

CURRENT_TIMESTAMP

Session

TIMESTAMP WITH TIME ZONE

LOCALTIMESTAMP

Session

TIMESTAMP

SYSDATE

Database server

DATE

SYSTIMESTAMP

Database server

TIMESTAMP WITH TIME ZONE

So which function should you use in a given situation? The answer depends on several factors, which you should probably consider in the following order:

  1. Whether you are using a release prior to Oracle8i Database or need to maintain compatibility with such a release. In either case, your choice is simple: use SYSDATE.

  2. Whether you are interested in the time on the database server or for your session. If for your session, then use a function that returns session time zone. If for the database server, then use a function that returns the database time zone.

  3. Whether you need the time zone to be returned as part of the current date and time. If so, then call either SYSTIMESTAMP ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required