sql 時區時間 Local Time Zone
--時區時間 Local Time Zone Geovin Du 塗聚文 --http://www.bigresource.com/MS_SQL-Converting-UTC-datetime-values-to-local-time-zones-XDKMI9sG.html SELECT GETUTCDATE() SELECT GETDATE() select CONVERT(varchar(100), GETUTCDATE(), 21) SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020') DECLARE @UTC DATETIME EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', 8 select @UTC DECLARE @UTC DATETIME,@d varchar(100) Set @d=CONVERT(varchar(100), GETUTCDATE(), 21) EXECUTE @UTC = UTCtoLocalDate @d, 8 select @UTC --時區存儲過程 Geovin Du 塗聚文 IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'UTCtoLocalDate') DROP PROCEDURE UTCtoLocalDate GO CREATE FUNCTION [dbo].[UTCtoLocalDate] ( @UTCDate DATETIME, --UTC標准時間 @TZ INT --時區 ) RETURNS DATETIME AS BEGIN IF ( DATEPART(hh, @UTCDate) <> 0 ) BEGIN DECLARE @LocalDate DATETIME DECLARE @UTCDelta INT DECLARE @thisYear INT DECLARE @DSTDay INT DECLARE @NormalDay INT DECLARE @DSTDate DATETIME DECLARE @NormalDate DATETIME SET @thisYear = YEAR(@UTCDate) IF (@thisYear < 2007 ) BEGIN SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1 SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7) SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM' SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM' END ELSE BEGIN SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 ) SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 ) SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM' SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM' END IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate)) BEGIN SET @UTCDelta = @TZ + 1 END ELSE BEGIN SET @UTCDelta = @TZ END -- now convert utc date to local date SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate) END ELSE BEGIN SET @LocalDate = @UTCDate END RETURN(@LocalDate) END GO /* 常用的几个时区: GMT:Greenwich Mean Time ET:U.S. Eastern Time -5 PST:Pacific Standard Time -8 GMT -11:00 Samoa GMT -10:00 U.S. Hawaiian Time GMT -09:30 Marquesas GMT -09:00 U.S. Alaska Time GMT -08:30 Pitcarn GMT -08:00 Pacific Time GMT -07:00 U.S. Mountain Time GMT -07:00 U.S. Mountain Time (Arizona) GMT -06:00 U.S. Central Time GMT -06:00 Mexico GMT -05:00 U.S. Eastern Time GMT -05:00 U.S. Eastern Time (Indiana) GMT -05:00 Columbia, Peru, South America GMT -04:00 Atlantic Time GMT -03:30 Newfoundland, Canada GMT -03:00 Argentina GMT -03:00 Brazil GMT -02:00 Mid-Atlantic GMT -01:00 Azores GMT U.K., Spain GMT +01:00 Western Europe GMT +02:00 Eastern Europe GMT +02:00 Egypt GMT +02:00 Israel GMT +03:00 Russia GMT +03:00 Saudi Arabia GMT +03:30 Iran GMT +04:00 Arabian GMT +04:30 Afghanistan GMT +05:00 Pakistan, West Asia GMT +05:30 India GMT +06:00 Bangladesh, Central Asia GMT +06:30 Burma GMT +07:00 Bangkok, Hanoi, Jakarta GMT +08:00 China, Taiwan GMT +08:00 China, beijing GMT +08:00 Singapore GMT +08:00 Australia (WT) GMT +09:00 Japan GMT +09:00 Korea GMT +09:30 Australia (CT) GMT +10:00 Australia (ET) GMT +10:30 Australia (Lord Howe) GMT +11:00 Central Pacific GMT +11:30 Norfolk Islands GMT +12:00 Fiji, New Zealand */
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)