Home » SQL & PL/SQL » SQL & PL/SQL » Adjust to local time
Adjust to local time [message #221316] Mon, 26 February 2007 04:26 Go to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Hi,
I have a table with a column which has dates recorded in GMT +0 and I want to display those dates in local time when fetching those data by a select command to view those records. I am running Oracle 9.2.0.7.0 on HP Unix.


e.g. I have a table below with dates in GMT +0

SQL> select ID, to_char(date1,'yyyy.mm.dd hh24:mi:ss') from table1;

ID TO_CHAR(DATE1,'
-- ----------------
01 2007.02.26 01:25:37
02 2007.02.26 06:11:22
03 2007.02.26 10:05:12



The database timezone is:

SQL> select dbtimezone from dual;

DBTIME
------
+09:00


I want to get all records with dates after 19:00 07/02/26 local time and display those dates as local time so I should get the output below:

ID TO_CHAR(DATE1,'
-- ----------------
03 2007.02.26 19:05:12


Any help will be appreciated.

Steve
Re: Adjust to local time [message #221324 is a reply to message #221316] Mon, 26 February 2007 05:14 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


 may be you can use the 
alter  session statement to change to the local time zone




regards,
Re: Adjust to local time [message #221454 is a reply to message #221316] Mon, 26 February 2007 21:12 Go to previous messageGo to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
The session's timezone is already in local timezone so setting the session timezone to local timezone does not solve this.

Is there a way to add the value of the dbtimezone or sessiontimezone to a date record in a table?

I was trying to do something like below but it did not work.

SQL> select id,
2 To_Char(Date1 + Dbtimezone/24,'Yyyy.Mm.Dd Hh24:Mi:Ss')
3 From Table1;

To_Char(Date1 + Dbtimezone/24,'Yyyy.Mm.Dd Hh24:Mi:Ss')
*
ERROR at line 2:
ORA-01722: invalid number
Re: Adjust to local time [message #221465 is a reply to message #221454] Mon, 26 February 2007 23:38 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Does this help

Ross Leishman
Re: Adjust to local time [message #221475 is a reply to message #221316] Tue, 27 February 2007 01:58 Go to previous message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
rleishman,
It worked!
This is exactly what I was looking for!
Thank you very much for your help!

Cheers
Steve
Previous Topic: Problem with Query
Next Topic: SQL
Goto Forum:
  


Current Time: Sat Dec 03 09:40:29 CST 2016

Total time taken to generate the page: 0.07206 seconds