Convert date format from UTC to PST in SQL

Tags:
Database Management Systems
SQL Server
Standard Edition 2000
T-SQL
HI Friends Is there any way to convert date format from UTC to PST? Thanks and Regards Ashu

Answer Wiki

Thanks. We'll let you know when a new response is added.

In which SQL?

Each vendor has its own TZ implementation or does not handle it.

The few I know:

Oracle (9) has a handy function:
– from utc to pst
SELECT NEW_TIME( to_date(’10-MAR-05 00:01:02′,’DD-MON-YY HH24:MI:SS’), ‘GMT’, ‘PST’) from dual;
– from East Daylight saving time to Pacific Std Time
SELECT NEW_TIME( to_date(’10-MAR-05 00:01:02′,’DD-MON-YY HH24:MI:SS’), ‘EDT’, ‘PST’) from dual;

09-Mar-05 16:01:02
09-Mar-05 20:01:02

MS-SQL (2000):
<Quote>Note that the SQL Server datetime data type does not use timezone and has a smaller precision than the XML time data type. To include the timezone data type or additional precision, store the data in SQL Server 2000 using a string type.</Quote>
GETUTCDATE() returns the current UTC time.

Postgresql (8):

FUCNTION: to_timestamp(text, text)
RETURNS: <timestamp with time zone>
DESC.: convert string to time stamp

SELECT to_timestamp( ’10-MAR-05 00:01:02 UTC’,’DD-MON-YY HH24:MI:SS TZ’)

Doc chapter: 9.9.3. AT TIME ZONE
FUNCTION: AT TIME ZONE
<timestamp without time zone> AT TIME ZONE <zone >
RETURNS <timestamp with time zone>
DESC.: Convert local time in given time zone to UTC

<timestamp with time zone> AT TIME ZONE <zone>
RETURNS: timestamp without time zone
DESC.: Convert UTC to local time in given time zone

<time with time zone> AT TIME ZONE <zone>
RETURNS: <time with time zone>
DESC.: Convert local time across time zones

Philippe.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ashug8bangalore
    [...] ... convert local time in given time zone to utc <timestamp with time zone> AT time ZONE ...http://itknowledgeexchange.techtarget.com/itanswers/convert-date-format-from-utc-to-pst-in-sql/CodeProject: Convert between UTC (Universal Co-ordinated Time) and ...How to convert between utc [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following