Link to home
Start Free TrialLog in
Avatar of hraja77
hraja77

asked on

oracle time zone

hi,

i have set my database timezone : ALTER DATABASE wdftr SET TIME_ZONE ='Canada/Pacific';
and now when i select sysdate from dual - its gives my the local time of my server - how do i query to get the time in canada

thanks
H
Avatar of Sean Stuber
Sean Stuber

Oracle recommends setting your database time zone  to "+0:00"  (UTC)

The database time zone has no effect on anything except TIMESTAMP WITH LOCAL TIME ZONE datatypes, and there is no functional advantage in picking one time zone over another except for some bugs when the time zone is anything other than +0:00


if you want your local time returned,  use CURRENT_DATE instead of SYSDATE
and set your SESSION time zone instead


alter session set time_zone = 'Canada/Pacific';

select current_date from dual;
Avatar of hraja77

ASKER

looks good - i guess i could set this up as a logon on trigger on one of my databases - as i have 2 databases on a UK server but one is used by the canadians and they need to use there own date/time;
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hraja77

ASKER

last question if thats ok - i keep getting this error - when i logon and the logon trigger is executed for my ws schema - the trigger creates fine and is below:
if you want i can assign the points and open a new question

C:\Documents and Settings\PCMSRajaH>sqlplus ws/ws@wdftr

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Nov 7 15:56:01 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION
ORA-06512: at line 7



CREATE OR REPLACE TRIGGER SYS.login_ws
AFTER LOGON
ON WS.SCHEMA
BEGIN
  execute immediate 'alter session set time_zone = "Canada/Pacific"';

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;
/
you used a single character double-quote  inside the execute immediate string.

to escape single quotes within a string you double them by using 2 single-quotes, not 1 double-quote


execute immediate 'alter session set time_zone = ''Canada/Pacific''';
Avatar of hraja77

ASKER

FANTASTIC !!
Avatar of hraja77

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for hraja77's comment #a38576455

for the following reason:

excellent
please accept one of the answers that helped you