Home » SQL & PL/SQL » SQL & PL/SQL » convert GMT TO day light saving time
convert GMT TO day light saving time [message #359381] Sat, 15 November 2008 10:12 Go to next message
NIckman
Messages: 64
Registered: May 2007
Member
Hi
I run reports every day using sql.
strtime defined as num(32)
I used this statement in my sql for start time.

(to_char(new_time(strtTme,'gmt','edt'),'MM/DD/YY HH24:MI:SS'))

my start time is ok until last week Nov2.In report now it is showing 1 hour more. now i need to change sql

(to_char(new_time(strtTme,'gmt','est'),'MM/DD/YY HH24:MI:SS'))

to get the time right.(edt TO est)
Is there any way with out manually change this script
and every november and every march need to cahnge edt to est(vice versa)
i do not want to change any setting of oracle db.
If i use alter session command still i need to change
alter session time.am i right.

but, when i run this command in sqlplus
select SYSTIMESTAMP from dual;
it is showing right time.
thx.N,


Re: convert GMT TO day light saving time [message #359382 is a reply to message #359381] Sat, 15 November 2008 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I remember Barbara already replied to this question witha neat answer. I can't remember which one it was but you can search, it is here somewhere.

Once you'll find it, please post the link.

Regards
Michel
Re: convert GMT TO day light saving time [message #359392 is a reply to message #359382] Sat, 15 November 2008 11:48 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Micheal,
Thanks for you reply.i really appreciated.
I searched the forms, i got this.
SCOTT@orcl_11g> SELECT NEW_TIME (the_date, 'GMT', 'est') AS est,
  2  	    NEW_TIME (the_date, 'GMT', 'EDT') AS edt,
  3  	    TO_CHAR (FROM_TZ (CAST (the_date AS TIMestAMP), 'GMT')
  4  	      AT TIME ZONE 'US/Eastern', 'DD-MON-YYYY HH24:MI') "US Eastern Time"
  5  FROM   (SELECT SYSDATE AS the_date FROM DUAL
  6  	     UNION ALL
  7  	     SELECT ADD_MONTHS (SYSDATE, 6) AS the_date FROM DUAL)
  8  /

est               EDT               US Eastern Time
----------------- ----------------- -----------------
20-JUN-2008 10:48 20-JUN-2008 11:48 20-JUN-2008 11:48
20-DEC-2008 10:48 20-DEC-2008 11:48 20-DEC-2008 10:48

SCOTT@orcl_11g> 



pl help me on this sql.
to get the result.
my sql is

select emp_name, 
to_char(new_time(srttime,'gmt','est'),'MM/DD/YY HH24:MI:SS'),
to_char(new_time(endtime,'gmt','est'),'MM/DD/YY HH24:MI:SS')
from 
emp,dept
where 
where empno = deptno
and emp_type = 'fulltime'
and hiretime >= TO_DATE('&sdate# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and endtime <= TO_DATE('&enddate# 23:59:59','YYYY-MM-DD HH24:MI:SS')
and emp_name like '%john%'
order by srttime;

how do i change this query using cast and from_tz.
pl help me.
thx, N.

Re: convert GMT TO day light saving time [message #359399 is a reply to message #359392] Sat, 15 November 2008 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where is your problem? Replace "the_date" by "strtime" or "endtime" name in your query.

Regards
Michel
Re: convert GMT TO day light saving time [message #359421 is a reply to message #359399] Sat, 15 November 2008 15:30 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Hi Michael,
pl help me.
i got this error.

ERROR at line 73:
ORA-00905: missing keyword


cat <<-EndOfCat
select emp_name, 

to_char(from_tz(cast(srttime),'gmt'),'MM/DD/YY HH24:MI:SS'),

to_char(from_tz(cast(endtime),'gmt'),'MM/DD/YY HH24:MI:SS)

from
 
emp,dept

where 
where empno = deptno
and emp_type = 'fulltime'
and hiretime >= TO_DATE('&sdate# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and endtime <= TO_DATE('&enddate# 23:59:59','YYYY-MM-DD HH24:MI:SS')
and emp_name like '%john%'
order by srttime;
SPOOL OFF
EndOfCat
Re: convert GMT TO day light saving time [message #359422 is a reply to message #359421] Sat, 15 November 2008 15:40 Go to previous message
NIckman
Messages: 64
Registered: May 2007
Member
Michael,
I got it . Thankss for your help.
Previous Topic: working with dates help
Next Topic: select statement help
Goto Forum:
  


Current Time: Fri Dec 09 23:29:41 CST 2016

Total time taken to generate the page: 0.23185 seconds