Question

Convert date format from UTC to PST in SQL

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

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.