SQL Convert to GMT DateTime

I will not convert the record 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, preferring not to use stored procs

thank

+2
sql time


source to share


6 answers


try it

To convert from GMT to local time:

select DATEADD(hour,DATEDIFF (hour, GETUTCDATE(), GETDATE()),MyGmtDateTime) as LocalDateTime

      



To convert from local time to GMT:

select DATEADD(hour,DATEDIFF (hour, GETDATE(), GETUTCDATE()),MyLocalDateTime) as GmtDateTime

      

+7


source to share


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

Take a look at this example where multiple time zones 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 that you asked for something else.



Well, if the offset in your dates is always the same, you can try this conversion.

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 will give you this idea. I don't have SQL 2008 on hand, so I haven't tested this. May not work.

+1


source to share


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

SELECT to_GMT(invoice_date) from INVOICES

      

+1


source to share


It is not clear from your question, but I am assuming you have the values ​​as a string. If yes, then extract everything but offset like datetime

, also get offset like datetime

, and then use 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);

      

+1


source to share


the solution will most likely depend on your RDBMS. In Oracle this will 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

      

+1


source to share


Use a SQL function CONVERT_TZ(dt,from_tz,to_tz)

.

CONVERT_TZ () converts the date / time value dt from the time zone specified by the from_tz parameter to the time zone specified by the to_tz parameter and returns the resulting value.

Example:

UPDATE this_table
SET this_table_date_gmt = (
    SELECT CONVERT_TZ(this_date, '+00:00', '-02:00')
)

      

Link: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

0


source to share







All Articles
Loading...
X
Show
Funny
Dev
Pics