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
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
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.
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
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);
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
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