Oracle LOCALTIMESTAMP Function

SQL LocalTimeStamp FunctionOracle LOCALTIMESTAMP Function returns current date and time of the current SQL session.
Oracle LOCALTIMESTAMP returns the TIMESTAMP value.


Oracle LOCALTIMESTAMP Function Syntax

SELECT LOCALTIMESTAMP
FROM table_name;

Oracle LOCALTIMESTAMP returns date and time as set by the ALTER SESSION statement.


Oracle LOCALTIMESTAMP – Using SQL SELECT Statement Example

Oracle LOCALTIMESTAMP is used with SQL SELECT Statement to fetch current date and timestamp.

For example, the below SQL SELECT query will return current date and timestamp.

SELECT LOCALTIMESTAMP
FROM DUAL;

May return something like “11/5/2012 12:09:00.807000 AM”


Oracle LOCALTIMESTAMP – Using ALTER SESSION Example

We can use the ALTER SESSION SET TIME_ZONE to change the time zone of the database.

For example, the SQL SELECT query below will return the date and time after we have altered the database timezone.

ALTER SESSION SET TIME_ZONE = '-7:0';

And then we query the LOCALTIMESTAMP as

SELECT LOCALTIMESTAMP
FROM DUAL;

May return something like “11/5/2012 12:09:21.186000 AM” because the time zone has been altered


Oracle LOCALTIMESTAMP – Using TRUNC Function Example

LOCALTIMESTAMP Function can be used with the SQL TRUNC Function to remove the timestamp part.

For example, the SQL query below returns only the date part from Oracle LOCALTIMESTAMP Function.

SELECT TRUNCT(localtimestamp)
FROM dual;

May return something like “11/5/2012”.


Tagged , , , , , , , , , , , . Bookmark the permalink.