April 26, 2014 at 12:57 am
I have the DB server in EST timezone.So all the dates do get stored in EST.
But in the procedure before processing the date, I need to convert the date
to Specific timezone dates i.e. to either PST or CST etc.
The info available with me is WindowsTimweZone as shown below:
000 Dateline Standard Time (GMT-12:00) International Date Line West
001 Samoa Standard Time (GMT-11:00) Midway Island, Samoa
002 Hawaiian Standard Time (GMT-10:00) Hawaii
003 Alaskan Standard Time (GMT-09:00) Alaska
004 Pacific Standard Time (GMT-08:00) Pacific Time (US and Canada); Tijuana
010 Mountain Standard Time (GMT-07:00) Mountain Time (US and Canada)
013 Mexico Standard Time 2 (GMT-07:00) Chihuahua, La Paz, Mazatlan
April 26, 2014 at 1:40 am
MVIT (4/26/2014)
I have the DB server in EST timezone.So all the dates do get stored in EST.But in the procedure before processing the date, I need to convert the date
to Specific timezone dates i.e. to either PST or CST etc.
The info available with me is WindowsTimweZone as shown below:
000 Dateline Standard Time (GMT-12:00) International Date Line West
001 Samoa Standard Time (GMT-11:00) Midway Island, Samoa
002 Hawaiian Standard Time (GMT-10:00) Hawaii
003 Alaskan Standard Time (GMT-09:00) Alaska
004 Pacific Standard Time (GMT-08:00) Pacific Time (US and Canada); Tijuana
010 Mountain Standard Time (GMT-07:00) Mountain Time (US and Canada)
013 Mexico Standard Time 2 (GMT-07:00) Chihuahua, La Paz, Mazatlan
To convert a date-time value from one timezone to another, simply add the offset difference using dateadd.
Normally the challenge is to correctly identify the originating timezone.
Introduced in SQL Server 2008 was the datetimeoffset data type which has timezone awareness, might be a slight overkill for your application.
😎
April 26, 2014 at 5:36 am
This is a simple version of timezone handling and time adjustments:
😎
USE tempdb;
GO
/*
NOTE: GMT is an obsolete standard, use UTC instead!
000 Dateline Standard Time (GMT-12:00) International Date Line West UTC Offset in Minutes: -720
001 Samoa Standard Time (GMT-11:00) Midway Island, Samoa UTC Offset in Minutes: -660
002 Hawaiian Standard Time (GMT-10:00) Hawaii UTC Offset in Minutes: -600
003 Alaskan Standard Time (GMT-09:00) Alaska UTC Offset in Minutes: -540
004 Pacific Standard Time (GMT-08:00) Pacific Time (US and Canada); Tijuana UTC Offset in Minutes: -480
010 Mountain Standard Time (GMT-07:00) Mountain Time (US and Canada) UTC Offset in Minutes: -420
013 Mexico Standard Time 2 (GMT-07:00) Chihuahua, La Paz, Mazatlan UTC Offset in Minutes: -420
*/
/* TIMEZONE DATA */
CREATE TABLE #TIMEZONE
(
TIMEZONE_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,TIMEZONE_CODE CHAR(4) NOT NULL
,TIMEZONE_NAME NVARCHAR(128) NOT NULL
,TIMEZONE_UTCOFFSET INT NOT NULL
);
INSERT INTO #TIMEZONE (TIMEZONE_CODE,TIMEZONE_NAME,TIMEZONE_UTCOFFSET)
VALUES
('IDL',N'Dateline Standard Time (GMT-12:00) International Date Line West',(-720))
,('SST',N'Samoa Standard Time (GMT-11:00) Midway Island, Samoa',(-660))
,('HAST',N'Hawaiian Standard Time (GMT-10:00) Hawaii',(-660))
,('AKST',N'Alaskan Standard Time (GMT-09:00) Alaska',(-540))
,('PST',N'Pacific Standard Time (GMT-08:00) Pacific Time (US and Canada); Tijuana',(-480))
,('MST',N'Mountain Standard Time (GMT-07:00) Mountain Time (US and Canada)',(-420))
,('MST2',N'Mexico Standard Time 2 (GMT-07:00) Chihuahua, La Paz, Mazatlan',(-420));
/* PSEUDO REPORT DATA */
CREATE TABLE #REPORTDATA
(
REPORTDATA_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,TIMEZONE_CODE CHAR(4) NOT NULL
,REPORTDATA_DATETIME DATETIME NOT NULL
,REPORTDATA_SOMEVALUE INT NOT NULL
);
INSERT INTO #REPORTDATA (TIMEZONE_CODE,REPORTDATA_DATETIME,REPORTDATA_SOMEVALUE)
VALUES
('IDL' ,'2012-04-01 08:25',1200)
,('SST' ,'2012-04-01 08:25',1200)
,('HAST','2012-04-01 08:25',1200)
,('AKST','2012-04-01 08:25',1200)
,('PST' ,'2012-04-01 08:25',1200)
,('MST' ,'2012-04-01 08:25',1200)
,('MST2','2012-04-01 08:25',1200)
,('HAST','2012-04-01 08:25',1200)
,('PST' ,'2012-04-01 08:25',1200);
/* REPORT TIMEZONE */
DECLARE @MYREPORT_TZOFFSET INT = (-270);
/* PSEUDO REPORT */
SELECT
RD.REPORTDATA_ID
,RD.REPORTDATA_DATETIME
,DATEADD(MINUTE,-TZ.TIMEZONE_UTCOFFSET,RD.REPORTDATA_DATETIME) AS REPORT_UTC
,DATEADD(MINUTE,(@MYREPORT_TZOFFSET),DATEADD(MINUTE,-TZ.TIMEZONE_UTCOFFSET,RD.REPORTDATA_DATETIME)) AS REPORT_MYTZ
,TZ.TIMEZONE_UTCOFFSET
,RD.TIMEZONE_CODE
FROM #REPORTDATA RD
INNER JOIN #TIMEZONE TZ
ON RD.TIMEZONE_CODE = TZ.TIMEZONE_CODE
/* CLEAN UP */
DROP TABLE #TIMEZONE;
DROP TABLE #REPORTDATA;
Edit: Corrected negated offset 😛
April 26, 2014 at 8:41 pm
Looks good, thanks for replying to my question. That help set off a light bulb lol. Now I can cross that one off my list, and move on to my next question.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply