Home » SQL & PL/SQL » SQL & PL/SQL » DBTIMEZONE questions
icon5.gif  DBTIMEZONE questions [message #225679] Tue, 20 March 2007 22:25 Go to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Hi,

I have a sql script which adds or subtracts time from a specific time:

select to_char(to_date('<TARGET_TIME>','YYYY.MM.DD hh24:mi:ss') <HOURS_TO_ADD_SUBTRACT> / 24, 'YYYY.MM.DD hh24:mi:ss') from dual;

e.g. To find the time 1.5 hours after 2005/02/10 13:30:00 :

SQL> select to_char(to_date('2005.02.10 13:30:00','YYYY.MM.DD hh24:mi:ss') + 1.5 / 24, 'YYYY.MM.DD hh24:mi:ss') from dual;

TO_CHAR(TO_DATE('20
-------------------
2005.02.10 15:00:00


I want to use the DB timezone value for <HOURS_TO_ADD_SUBTRACT> field but I don't know how.

e.g. If the DB timezone value is -4 as below then I want to use -4 for <HOURS_TO_ADD_SUBTRACT> field.

SQL> select dbtimezone from dual;

DBTIME
------
-04:00


Could someone please tell me how I can use this dbtimezone value inside my script?

Any help will be greatly appreciated.

Steve

[Updated on: Wed, 21 March 2007 00:32]

Report message to a moderator

Re: DBTIMEZONE questions [message #225710 is a reply to message #225679] Wed, 21 March 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if there is a function that gives you part of dbtimezone but you can use:
SQL> select dbtimezone,
  2         to_number(substr(dbtimezone,1,instr(dbtimezone,':')-1)) hour,
  3         to_number(substr(dbtimezone,instr(dbtimezone,':')+1)) minute
  4  from dual;
DBTIME       HOUR     MINUTE
------ ---------- ----------
+02:00          2          0

Regards
Michel
Re: DBTIMEZONE questions [message #225789 is a reply to message #225710] Wed, 21 March 2007 09:02 Go to previous message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Thanx Michel,

I'll look into the "substr" command and see if I can use it for my script to work.

cheers

Steve
Previous Topic: How do I total 2 total columns in this SQL??
Next Topic: problem with evaluation of function
Goto Forum:
  


Current Time: Fri Dec 09 17:32:05 CST 2016

Total time taken to generate the page: 0.16810 seconds