dnwaUhpD

1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TimeZoneInfo') AND type IN (N'U'))
2  DROP TABLE dbo.TimeZoneInfo
3
4CREATE TABLE dbo.TimeZoneInfo (
5  TimeZoneID int IDENTITY(1, 1) NOT NULL,
6  Display nvarchar(255) NOT NULL,
7  Bias smallint NOT NULL,
8  StdBias smallint NOT NULL,
9  DltBias smallint NOT NULL,
10  StdMonth smallint NOT NULL,
11  StdDayOfWeek smallint NOT NULL,
12  StdWeek smallint NOT NULL,
13  StdHour smallint NOT NULL,
14  DltMonth smallint NOT NULL,
15  DltDayOfWeek smallint NOT NULL,
16  DltWeek smallint NOT NULL,
17  DltHour smallint NOT NULL,
18
19  CONSTRAINT PK_TimeZoneInfo PRIMARY KEY CLUSTERED (TimeZoneID ASC)
20)
21GO
22
23IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetDaylightStandardDateTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
24  DROP FUNCTION dbo.GetDaylightStandardDateTime
25GO
26
27CREATE FUNCTION GetDaylightStandardDateTime
28(
29  @Year int, -- a valid year value
30  @Month int, -- 1..12
31  @DayOfWeek smallint, -- 1..7
32  @Week smallint, -- 1..5, 1 - first week, 2 - second, etc.,  5 - the last week
33  @Hour smallint -- hour value when daylight or standard time begins.
34)
35RETURNS datetime
36AS
37BEGIN
38DECLARE @FirstOfMonth datetime;
39DECLARE @DoW smallint;
40DECLARE @Ret datetime;
41
42-- find day of the week of the first day of a given month:
43SET @FirstOfMonth = CAST(@Year AS NVARCHAR) + '/' +  CAST(@Month AS NVARCHAR) + '/01';
44
45-- 5th week means the last week of the month, so go one month forth, then one week back
46IF @Week = 5
47  SET @FirstOfMonth = DATEADD(Month, 1, @FirstOfMonth);
48
49SET @DoW = DATEPART(weekday, @FirstOfMonth);
50
51-- find first given day of the week of the given month:
52IF @DoW > @DayOfWeek
53  SET @Ret = DATEADD(Day, 7 + @DayOfWeek - @DoW, @FirstOfMonth)
54ELSE
55  SET @Ret = DATEADD(Day, @DayOfWeek - @DoW, @FirstOfMonth);
56
57-- advance to the given week (5th week means the last one of the month)
58IF @Week < 5
59  SET @Ret = DATEADD(Week, @Week - 1, @Ret)
60ELSE
61-- the last week of the previous month; go one week backward
62  SET @Ret = DATEADD(Week, -1, @Ret);
63
64SET @Ret = DATEADD(Hour, @Hour, @Ret);
65
66RETURN @Ret
67END
68
69GO
70
71IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetLocalDateTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
72  DROP FUNCTION dbo.GetLocalDateTime
73GO
74
75CREATE FUNCTION dbo.GetLocalDateTime
76(
77  @UTCDate datetime,
78  @TimeZoneID smallint
79)
80RETURNS datetime
81AS
82BEGIN
83DECLARE @LocalDateTime datetime
84DECLARE @DltBiasFactor smallint
85
86DECLARE @Display nvarchar(50)
87DECLARE @Bias int
88DECLARE @DltBias int
89DECLARE @StdMonth smallint
90DECLARE @StdDow smallint
91DECLARE @StdWeek smallint
92DECLARE @StdHour smallint
93DECLARE @DltMonth smallint
94DECLARE @DltDow smallint
95DECLARE @DltWeek smallint
96DECLARE @DltHour smallint
97
98DECLARE @DaylightDate datetime
99DECLARE @StandardDate datetime
100
101SET @DltBiasFactor = 0
102
103SELECT @Display = Display,
104@Bias = (-1 * Bias), 
105@DltBias = (-1 * DltBias),
106@StdMonth  = StdMonth,
107@StdDow = StdDayOfWeek + 1,
108@StdWeek = StdWeek,
109@StdHour = StdHour,
110@DltMonth = DltMonth,
111@DltDow = DltDayOfWeek + 1,
112@DltWeek = DltWeek,
113@DltHour = DltHour
114FROM dbo.TimeZoneInfo 
115WHERE TimeZoneID = @TimeZoneID
116
117IF @StdMonth = 0
118BEGIN
119  SET @LocalDateTime = DateAdd(minute, @Bias, @UTCDate)
120END ELSE BEGIN
121  SET @StandardDate =  dbo.GetDaylightStandardDateTime(DATEPART(year, @UTCDate), @StdMonth, @StdDow, @StdWeek, @StdHour)
122  SET @DaylightDate = dbo. GetDaylightStandardDateTime(DATEPART(year, @UTCDate), @DltMonth, @DltDow, @DltWeek, @DltHour)
123
124	
125  IF ( @StandardDate > @DaylightDate)
126  BEGIN
127    IF (DATEADD(minute, @Bias, @UTCDate)  BETWEEN @DaylightDate AND @StandardDate  )
128      SET @DltBiasFactor = 1
129  END ELSE BEGIN
130    IF (DATEADD(minute, @Bias, @UTCDate)  BETWEEN @StandardDate AND @DaylightDate)
131      SET @DltBiasFactor = 0
132  END
133
134  SET @LocalDateTime = DATEADD(minute, @Bias + (@DltBiasFactor * @DltBias), @UTCDate)
135END
136
137RETURN  @LocalDateTime;
138
139END 
140GO
141
142TRUNCATE TABLE dbo.TimeZoneInfo
143INSERT INTO dbo.TimeZoneInfo
144([Display],[Bias],[StdBias],[DltBias],[StdMonth],[StdDayOfWeek],[StdWeek],[StdHour],[DltMonth],[DltDayOfWeek],[DltWeek],[DltHour])
145VALUES
146  ('(GMT-12:00) International Date Line West',720,0,-60,0,0,0,0,0,0,0,0)
147, ('(GMT-11:00) Midway Island, Samoa',660,0,-60,0,0,0,0,0,0,0,0)
148, ('(GMT-10:00) Hawaii',600,0,-60,0,0,0,0,0,0,0,0)
149, ('(GMT-09:00) Alaska',540,0,-60,10,0,5,2,4,0,1,2)
150, ('(GMT-08:00) Pacific Time (US & Canada); Tijuana',480,0,-60,10,0,5,2,4,0,1,2)
151, ('(GMT-07:00) Chihuahua, La Paz, Mazatlan',420,0,-60,10,0,5,2,4,0,1,2)
152, ('(GMT-07:00) Mountain Time (US & Canada)',420,0,-60,10,0,5,2,4,0,1,2)
153, ('(GMT-07:00) Arizona',420,0,-60,0,0,0,0,0,0,0,0)
154, ('(GMT-06:00) Guadalajara, Mexico City, Monterrey',360,0,-60,10,0,5,2,4,0,1,2)
155, ('(GMT-06:00) Saskatchewan',360,0,-60,0,0,0,0,0,0,0,0)
156, ('(GMT-06:00) Central America',360,0,-60,0,0,0,0,0,0,0,0)
157, ('(GMT-06:00) Central Time (US & Canada)',360,0,-60,10,0,5,2,4,0,1,2)
158, ('(GMT-05:00) Eastern Time (US & Canada)',300,0,-60,10,0,5,2,4,0,1,2)
159, ('(GMT-05:00) Bogota, Lima, Quito',300,0,-60,0,0,0,0,0,0,0,0)
160, ('(GMT-05:00) Indiana (East)',300,0,-60,0,0,0,0,0,0,0,0)
161, ('(GMT-04:00) Caracas, La Paz',240,0,-60,0,0,0,0,0,0,0,0)
162, ('(GMT-04:00) Santiago',240,0,-60,3,6,2,0,10,6,2,0)
163, ('(GMT-04:00) Atlantic Time (Canada)',240,0,-60,10,0,5,2,4,0,1,2)
164, ('(GMT-03:30) Newfoundland',210,0,-60,10,0,5,2,4,0,1,2)
165, ('(GMT-03:00) Buenos Aires, Georgetown',180,0,-60,0,0,0,0,0,0,0,0)
166, ('(GMT-03:00) Brasilia',180,0,-60,2,0,2,2,10,0,3,2)
167, ('(GMT-03:00) Greenland',180,0,-60,10,0,5,2,4,0,1,2)
168, ('(GMT-02:00) Mid-Atlantic',120,0,-60,9,0,5,2,3,0,5,2)
169, ('(GMT-01:00) Azores',60,0,-60,10,0,5,3,3,0,5,2)
170, ('(GMT-01:00) Cape Verde Is.',60,0,-60,0,0,0,0,0,0,0,0)
171, ('(GMT) Casablanca, Monrovia',0,0,-60,0,0,0,0,0,0,0,0)
172, ('(GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London',0,0,-60,10,0,5,2,3,0,5,1)
173, ('(GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague',-60,0,-60,10,0,5,3,3,0,5,2)
174, ('(GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb',-60,0,-60,10,0,5,3,3,0,5,2)
175, ('(GMT+01:00) Brussels, Copenhagen, Madrid, Paris',-60,0,-60,10,0,5,3,3,0,5,2)
176, ('(GMT+01:00) West Central Africa',-60,0,-60,0,0,0,0,0,0,0,0)
177, ('(GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna',-60,0,-60,10,0,5,3,3,0,5,2)
178, ('(GMT+02:00) Harare, Pretoria',-120,0,-60,0,0,0,0,0,0,0,0)
179, ('(GMT+02:00) Jerusalem',-120,0,-60,0,0,0,0,0,0,0,0)
180, ('(GMT+02:00) Athens, Beirut, Istanbul, Minsk',-120,0,-60,10,0,5,3,3,0,5,2)
181, ('(GMT+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius',-120,0,-60,10,0,5,4,3,0,5,3)
182, ('(GMT+02:00) Bucharest',-120,0,-60,10,0,5,1,3,0,5,0)
183, ('(GMT+02:00) Cairo',-120,0,-60,9,3,5,2,5,5,1,2)
184, ('(GMT+03:00) Nairobi',-180,0,-60,0,0,0,0,0,0,0,0)
185, ('(GMT+03:00) Kuwait, Riyadh',-180,0,-60,0,0,0,0,0,0,0,0)
186, ('(GMT+03:00) Baghdad',-180,0,-60,10,0,1,4,4,0,1,3)
187, ('(GMT+03:00) Moscow, St. Petersburg, Volgograd',-180,0,-60,10,0,5,3,3,0,5,2)
188, ('(GMT+03:30) Tehran',-210,0,-60,9,2,4,2,3,0,1,2)
189, ('(GMT+04:00) Abu Dhabi, Muscat',-240,0,-60,0,0,0,0,0,0,0,0)
190, ('(GMT+04:00) Baku, Tbilisi, Yerevan',-240,0,-60,10,0,5,3,3,0,5,2)
191, ('(GMT+04:30) Kabul',-270,0,-60,0,0,0,0,0,0,0,0)
192, ('(GMT+05:00) Ekaterinburg',-300,0,-60,10,0,5,3,3,0,5,2)
193, ('(GMT+05:00) Islamabad, Karachi, Tashkent',-300,0,-60,0,0,0,0,0,0,0,0)
194, ('(GMT+05:30) Chennai, Kolkata, Mumbai, New Delhi',-330,0,-60,0,0,0,0,0,0,0,0)
195, ('(GMT+05:45) Kathmandu',-345,0,-60,0,0,0,0,0,0,0,0)
196, ('(GMT+06:00) Almaty, Novosibirsk',-360,0,-60,10,0,5,3,3,0,5,2)
197, ('(GMT+06:00) Sri Jayawardenepura',-360,0,-60,0,0,0,0,0,0,0,0)
198, ('(GMT+06:00) Astana, Dhaka',-360,0,-60,0,0,0,0,0,0,0,0)
199, ('(GMT+06:30) Rangoon',-390,0,-60,0,0,0,0,0,0,0,0)
200, ('(GMT+07:00) Krasnoyarsk',-420,0,-60,10,0,5,3,3,0,5,2)
201, ('(GMT+07:00) Bangkok, Hanoi, Jakarta',-420,0,-60,0,0,0,0,0,0,0,0)
202, ('(GMT+08:00) Kuala Lumpur, Singapore',-480,0,-60,0,0,0,0,0,0,0,0)
203, ('(GMT+08:00) Taipei',-480,0,-60,0,0,0,0,0,0,0,0)
204, ('(GMT+08:00) Irkutsk, Ulaan Bataar',-480,0,-60,10,0,5,3,3,0,5,2)
205, ('(GMT+08:00) Beijing, Chongqing, Hong Kong, Urumqi',-480,0,-60,0,0,0,0,0,0,0,0)
206, ('(GMT+08:00) Perth',-480,0,-60,0,0,0,0,0,0,0,0)
207, ('(GMT+09:00) Yakutsk',-540,0,-60,10,0,5,3,3,0,5,2)
208, ('(GMT+09:00) Seoul',-540,0,-60,0,0,0,0,0,0,0,0)
209, ('(GMT+09:00) Osaka, Sapporo, Tokyo',-540,0,-60,0,0,0,0,0,0,0,0)
210, ('(GMT+09:30) Darwin',-570,0,-60,0,0,0,0,0,0,0,0)
211, ('(GMT+09:30) Adelaide',-570,0,-60,3,0,5,3,10,0,5,2)
212, ('(GMT+10:00) Canberra, Melbourne, Sydney',-600,0,-60,3,0,5,3,10,0,5,2)
213, ('(GMT+10:00) Brisbane',-600,0,-60,0,0,0,0,0,0,0,0)
214, ('(GMT+10:00) Guam, Port Moresby',-600,0,-60,0,0,0,0,0,0,0,0)
215, ('(GMT+10:00) Hobart',-600,0,-60,3,0,5,3,10,0,1,2)
216, ('(GMT+10:00) Vladivostok',-600,0,-60,10,0,5,3,3,0,5,2)
217, ('(GMT+11:00) Magadan, Solomon Is., New Caledonia',-660,0,-60,0,0,0,0,0,0,0,0)
218, ('(GMT+12:00) Fiji, Kamchatka, Marshall Is.',-720,0,-60,0,0,0,0,0,0,0,0)
219, ('(GMT+12:00) Auckland, Wellington',-720,0,-60,3,0,3,2,10,0,1,2)
220, ('(GMT+13:00) Nuku''alofa',-780,0,-60,0,0,0,0,0,0,0,0)
221GO
222
223/*
224Example how to convert Local time to UTC time.
225--*/
226DECLARE @TimeZoneID smallint = 27; -- Dublin
227SELECT dbo.GetLocalDateTime(GETUTCDATE(), @TimeZoneID);
228SELECT dbo.GetLocalDateTime('2014-07-01', @TimeZoneID);
229SELECT dbo.GetLocalDateTime('2014-01-01', @TimeZoneID);