User login
Sponsored links
Get Current Time in a Different Time Zone
Mon, 07/21/2008 - 9:21am — Lepa
And here is another one with better formatted time stamp.
A complete list of available time zones can be found by selecting from v$timezone_names table.
It is relatively easy to select current_timestamp from dual and get current date time in your time zone. But things get a little more complicated when you want to get date and time in a different time zone. Here is how you can accomplish that:
-- Get current date and time as well as current date and time in a different time zone (using eastern time zone).
SELECT CURRENT_TIMESTAMP currt_dt,
CURRENT_TIMESTAMP AT TIME ZONE TZ_OFFSET ('EST') different_dt
FROM DUAL;
SELECT CURRENT_TIMESTAMP currt_dt,
CURRENT_TIMESTAMP AT TIME ZONE TZ_OFFSET ('EST') different_dt
FROM DUAL;
CURRT_DT DIFFERENT_DT
----------------------------------- -----------------------------------
21-JUL-08 09.13.04.431522 AM -05:00 21-JUL-08 10.13.04.431522 AM -04:00
1 row selected.
----------------------------------- -----------------------------------
21-JUL-08 09.13.04.431522 AM -05:00 21-JUL-08 10.13.04.431522 AM -04:00
1 row selected.
-- Same as above but with no time zone display (-05:00)
SELECT LOCALTIMESTAMP currt_dt, LOCALTIMESTAMP AT TIME ZONE TZ_OFFSET ('EST') different_dt
FROM DUAL;
SELECT LOCALTIMESTAMP currt_dt, LOCALTIMESTAMP AT TIME ZONE TZ_OFFSET ('EST') different_dt
FROM DUAL;
CURRT_DT DIFFERENT_DT
------------------------------- -----------------------------------
21-JUL-08 09.15.37.211514 AM 21-JUL-08 10.15.37.211514 AM -04:00
1 row selected.
------------------------------- -----------------------------------
21-JUL-08 09.15.37.211514 AM 21-JUL-08 10.15.37.211514 AM -04:00
1 row selected.
And here is another one with better formatted time stamp.
SELECT TO_CHAR ((LOCALTIMESTAMP AT TIME ZONE TZ_OFFSET ('EST')),
'MM/DD/YYYY HH:MI:SS') different_zone_dt
FROM DUAL;
'MM/DD/YYYY HH:MI:SS') different_zone_dt
FROM DUAL;
DIFFERENT_ZONE_DT
-------------------
07/21/2008 10:17:47
1 row selected.
-------------------
07/21/2008 10:17:47
1 row selected.
A complete list of available time zones can be found by selecting from v$timezone_names table.
SELECT DISTINCT tzname
FROM v$timezone_names;
FROM v$timezone_names;
Bookmark/Search this post with:
Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!
Unanswered Forum Posts
| Title | Under | Posted on |
|---|---|---|
| Regarding Error "SQR 6002) CANNOT OPEN THE PRINTER FILE: " in SQR | PeopleSoft Technical | 11/21/2008 - 1:17am |
| Pay calendar Setup | PeopleSoft Functional | 11/18/2008 - 5:30pm |
| Issue linking to Excel 2007 from PS HRMS 8.8 | PeopleSoft Technical | 11/17/2008 - 9:48am |
| Spreadsheet-Based Control System Analysis and Design | Programming | 11/11/2008 - 5:10am |
Stay updated
CompShack is a user driven Dev2Dev IT community. Developers can ask questions, help answer questions, or just browse code and articles contributed by users. No registration required.
You can register if you want to contribute code samples, share your knowledge at your own blog, and become adored in the community!
About Lepa
Job title
Application Developer
IT skills
PeopleSoft, SQL, SQR, HTML, PHP
Comments
Re: Get Current Time in a Different Time Zone
Join date: 06/30/08
If want to to same thing in PeopleSoft , this can help
DateTimeToTimeZone
DateTimeToTimeZone(OldDateTime, SourceTimeZone, DestinationTimeZone)
&NEWDATETIME = DateTimeToTimeZone(TESTDTTM, "PST", "EST");
Re: Get Current Time in a Different Time Zone
Join date: 06/23/08
Thanks Nitin, why not submit that as a Code Sample to your blog?! :)
Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog
Post new comment