SQL Convert to GMT DateTime

Question

Hi there,

i wan't to convert my records entries from dateTtime+OFFSET to dateTtimeZ directly

(2008-05-11T15:30:00+2--->2008-05-11T13:30:00Z)

with sql functions.

Don't know how to do this :-(

I need to implement this using MySql prefering not using stored procs

Thx

Answer 1

If I understood your question correctly, there is no way to find out the time zone from time+offset because the mapping is not unique. Several time zones may have the same offset.

Look at this example where multiple time zone have the same offset.

(GMT-06:00) Saskatchewan
(GMT-06:00) Guadalajara, Mexico City, Monterrey - Old
(GMT-06:00) Guadalajara, Mexico City, Monterrey - New
(GMT-06:00) Central Time (US & Canada)
(GMT-06:00) Central America

ADDED: Now I see you asked for something else.

Okay, if the offset in your datetimes is always the same, you can try this transformation.

DECLARE @DateTimeWithOffset datetimeoffset
DECLARE @JustDateTime datetime

SET @DateTimeWithOffset = '2008-05-11 15:30:00 + 02:00'

SELECT @DateTimeWithOffset = SWITCHOFFSET (@DateTimeWithOffset, '00:00')

SELECT @JustDateTime = CAST (@DateTimeWithOffset AS datetime)

This is to give you the idea. I don't have an SQL 2008 right at hand so I haven't tested that. May not work.

Answer 2

I'm pretty rusty with my SQL built-in functions, but in the absence of any standard function, you could write a stored procedure to do the work. Then you could invoke it as part of your SELECT statement:

SELECT to_GMT(invoice_date) from INVOICES

Answer 3

It is not clear from your question, but I assume you have the values as a string. If so, then extract everything except the offset as a datetime, also get the offset as a datetime, then use the sign to calculate the final result (T-SQL, 2005):

DECLARE @withOffset varchar(30);
SET @withOffset = '2008-05-11T15:30:00+2:00';
PRINT N'Original: ' + CAST(@withOffset AS nvarchar);

DECLARE @dt datetime;
SET @dt = CONVERT(datetime, LEFT(@withOffset, 19), 126);
PRINT N'dt=' + CONVERT(nvarchar, @dt, 127);

DECLARE @ofs datetime;
SET @ofs = CONVERT(datetime, SUBSTRING(@withOffset, 21, LEN(@withOffset) - 21), 108);
PRINT N'ofs=' + CAST(@ofs AS nvarchar);

IF (SUBSTRING(@withOffset, 19, 1) = '+')
BEGIN
    SET @dt = DATEADD(hour, DATEPART(hour, @ofs), @dt);
    SET @dt = DATEADD(minute, DATEPART(minute, @ofs), @dt);
END
ELSE
BEGIN
    SET @dt = DATEADD(hour, -DATEPART(hour, @ofs), @dt);
    SET @dt = DATEADD(minute, -DATEPART(minute, @ofs), @dt);
END;

PRINT N'dt=' + CONVERT(nvarchar, @dt, 127);

Answer 4

Hi Lici,

the solution will likely depend of your RDBMS. In Oracle this would work:

SQL> SELECT to_timestamp_tz('2008-05-11T15:30:00+2',
  2                         'yyyy-mm-dd"T"hh24:mi:ssTZH')
  3         AT TIME ZONE 'GMT' gmt_time
  4    FROM dual;

GMT_TIME
----------------------------------
11/05/08 13:30:00,000000000 GMT