1 person found this helpful
This forum, as the title says, is ONLY for sql developer questions and NOT for sql or PL/SQL questions.
Please mark this thread ANSWERED and repost in the SQL and PL/SQL forum.
I have two databases with the data stored from the same source. One database stores the time as GMT and the other stores the time as local time (MST). MST = GMT - 7 (or -6 depending the DST). My task is to compare the values in the two databases for the time field. I converted the GMT time to local time using the New_TIME function as below:
Select NEW_TIME(TO_DATE('05-10-2010 05:00', 'MM-DD-YYYY HH24:MI), 'GMT', 'MST') "DATETIME" FROM DUAL;
Well using the NEW_TIME function is the problem. Did you RTFM for that function?
See Table 8-1 - It shows you, as your own example also shows, that the timezones you can specify INCLUDE a specific indicator for each standard or daylight time. That limits the usefulness of that function.
Use the FROM_TZ function - it doesn't have that limitation.
That doc section shows how to use the AT TIME ZONE clause to convert from one time zone to another.
For your use case you should just convert to GMT and then compare directly.
If you need any additional help repost the problem in the appropriate forum.
Thanks for the reply. I have moved my question to the correct forum.