sql 時區時間 Local Time Zone

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132

--時區時間 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 */