Home » SQL & PL/SQL » SQL & PL/SQL » How to get Local System date ?
How to get Local System date ? [message #211888] Tue, 02 January 2007 23:33 Go to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
I am working on the client (oracle 8i client) connected to the server.

i want the local time to be captured. not server time

'sysdate' will gives the server date

but i want client systems date

is it possible ?

regards
muthu


Re: How to get Local System date ? [message #211894 is a reply to message #211888] Wed, 03 January 2007 00:13 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In SQL*Plus, you can get the client date to appear in a report title with the _DATE variable, but you cannot use it in a SELECT statement.

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> ttitle center _date
SQL> select sysdate from dual
  2  /

                              03-JAN-2007 16:58:47
SYSDATE
--------------------
03-JAN-2007 17:00:15

SQL> 


You can be a bit tricky and write the date to a file, then read it back in as a variable:

SQL> alter session set nls_date_format = 'YYYYMMDDHH24MISS';

Session altered.

SQL> 
SQL> ttitle left "define client_date=" _date
SQL> 
SQL> set heading off
SQL> 
SQL> spool cdate.sql
SQL> select NULL 
  2  from dual
  3  /

define client_date=20070103170748


SQL> spool off
SQL> 
SQL> @cdate
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SQL> 
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> set heading on
SQL> ttitle off
SQL> 
SQL> select to_date(&client_date, 'YYYYMMDDHH24MISS') as client_date
  2  , sysdate as server_date
  3  from dual;
old   1: select to_date(&client_date, 'YYYYMMDDHH24MISS') as client_date
new   1: select to_date(20070103170748, 'YYYYMMDDHH24MISS') as client_date

CLIENT_DATE          SERVER_DATE
-------------------- --------------------
03-JAN-2007 17:07:48 03-JAN-2007 17:09:19

SQL> 


If you are using some other client program (other than SQL*Plus) then you have to use some feature of that program. Since SQL and PL/SQL run on the server, it is IMPOSSIBLE for those languages to retrieve the client date; it MUST be done by the client tool/language.

Ross Leishman
Previous Topic: Triggers
Next Topic: querying based on the string data in the column
Goto Forum:
  


Current Time: Mon Dec 05 21:05:12 CST 2016

Total time taken to generate the page: 0.13024 seconds