--**************************************
--     
-- Name: UPDATED: Example of Registry Pi
--     cking
-- Description:UPDATED! Dips int registr
--     y to get local Timezone information, con
--     verts and displays in DATETIME format.
-- By: John Overton
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=544&lngWId;=5--for details.--**************************************
--     

DECLARE @STR VARCHAR (255)--DATA FROM REGISTRY
DECLARE @MONTH VARCHAR(2)
DECLARE @DAYOFWEEK VARCHAR(12)
DECLARE @WEEK VARCHAR(2)
DECLARE @HOUR VARCHAR(2)
DECLARE @CONMONTH INT
DECLARE @CONDAYOFWEEK VARCHAR(10)
DECLARE @CONWEEK INT
DECLARE @CONHOUR INT
DECLARE @DATA BINARY(32)
DECLARE @VALUE VARCHAR(32)
DECLARE @CURDATE DATETIME
DECLARE @CURFALL DATETIME
DECLARE @CURSPRING DATETIME
DECLARE @WEEKCOUNT INT
CREATE TABLE #DST ( Value nvarchar( 255 ) , Data binary(32) )
INSERT #DST
EXECUTE master.dbo.xp_regread
'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'DAYLIGHTSTART' 
INSERT #DST
EXECUTE master.dbo.xp_regread
'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'STANDARDSTART' 
--CURSOR FOR CONVERTING CURRENT TIMEZONE
--      DATA
DECLARE CUR CURSOR FOR
SELECT VALUE, DATA FROM #DST 
OPEN CUR 
FETCH FROM CUR INTO @VALUE, @DATA
WHILE @@FETCH_STATUS =0
    BEGIN
    	IF @@FETCH_STATUS<>-1
    	BEGIN	
    		EXEC master..xp_varbintohexstr @DATA, @STR out
    		SET @MONTH=(select substring(@str,8,1))
    		SET @MONTH=( SELECT CASE @MONTH
    			WHEN '0' THEN 0
    			WHEN '1' THEN 1
    			WHEN '2' THEN 2
    			WHEN '3' THEN 3
    			WHEN '4' THEN 4
    			WHEN '5' THEN 5
    			WHEN '6' THEN 6
    			WHEN '7' THEN 7
    			WHEN '8' THEN 8
    			WHEN '9' THEN 9
    			WHEN 'a' THEN 10
    			WHEN 'b' THEN 11
    			WHEN 'c' THEN 12
    			WHEN 'd' THEN 13
    			WHEN 'e' THEN 14
    			WHEN 'f' THEN 15
     			END )
    		SET @DAYOFWEEK=(SELECT CASE SUBSTRING(@STR,9,2)
    			WHEN 00 THEN 'Sunday'
    			WHEN 01 THEN 'Monday'
    			WHEN 02 THEN 'Tuesday'
    			WHEN 03 THEN 'Wednesday'
    			WHEN 04 THEN 'Thursday'
    			WHEN 05 THEN 'Friday'
    			WHEN 06 THEN 'Saturday'
    			END )
    		SET @WEEK=(SELECT SUBSTRING(@STR,11,2))
    		SET @HOUR=(SELECT SUBSTRING(@STR,15,2))
    		--CAST TO CONVERT TO INTEGER
    		SET @CONMONTH=CAST(@MONTH AS INT)
    		SET @CONWEEK=CAST(@WEEK AS INT)
    		SET @CONHOUR=CAST(@HOUR AS INT)
    	--CHECK TO SEE IF LOCAL HOST IS USING DST
    		IF @CONMONTH<>0 
    		BEGIN
    		---CONVERT DATA TO DATE FORM
    			--CURRENT CONVERSION
    			SET @CURDATE=CAST(STR(@CONMONTH)+'/01/'+
    STR(YEAR(GETDATE())) AS DATETIME)
    			SET @WEEKCOUNT=0
    			IF @CONWEEK=5
    			BEGIN
    				SET
    @CURDATE=DATEADD(DAY,-1,DATEADD(MONTH,1,@CURDATE))
    				WHILE DATENAME(DW,@CURDATE)<>@DAYOFWEEK
    				SET @CURDATE=DATEADD(DAY,-1,@CURDATE)
    			END
    			ELSE
    			BEGIN
    				WHILE DATENAME(DW,@CURDATE)<>@DAYOFWEEK
    				BEGIN
    					SET @CURDATE=DATEADD(DAY,1,@CURDATE)
    				END
    				SET @WEEKCOUNT=1
    				WHILE @WEEKCOUNT<>@CONWEEK
    				BEGIN
    					SET
    @CURDATE=DATEADD(WEEK,1,@CURDATE)
    					SET @WEEKCOUNT =@WEEKCOUNT + 1
    				END
    			END
    			SET @CURDATE = DATEADD(HH,@CONHOUR,@CURDATE)
    			IF @VALUE='DAYLIGHTSTART'
    			BEGIN
    				SET @CURSPRING=@CURDATE
    			END
    			ELSE
    			BEGIN
    				SET @CURFALL=@CURDATE
    			END
    		END
    		--END CURRENT CONVERSION	
    		FETCH NEXT FROM CUR INTO @VALUE, @DATA
    	END
END

CLOSE CUR
DEALLOCATE CUR
--END OF CURRENT TIME ZONE CONVERSION
DROP TABLE #dst
SELECT @curspring AS 'SPRING CHANGE',@CURFALL AS 'FALL CHANGE'