Log Stamps

The SyncStamp is base 36, (0-9 and A-Z), add up the number and divide by 50
gives you the seconds since 12/31/1989 at 8pm(not sure if that is a timezone
thing, 1/1/1990 00:00 seems more reasonable, but hey its GoldMine).




CREATE FUNCTION fn_SyncStamp(@STR VARCHAR(7))
RETURNS DATETIME
AS
BEGIN

-- CREATE TABLE VARIABLE FOR DATE STRING CONVERSION
DECLARE @alpha TABLE (c char, val int IDENTITY(0,1))

INSERT INTO @alpha (c) VALUES( '0')
INSERT INTO @alpha (c) VALUES( '1')
INSERT INTO @alpha (c) VALUES( '2')
INSERT INTO @alpha (c) VALUES( '3')
INSERT INTO @alpha (c) VALUES( '4')
INSERT INTO @alpha (c) VALUES( '5')
INSERT INTO @alpha (c) VALUES( '6')
INSERT INTO @alpha (c) VALUES( '7')
INSERT INTO @alpha (c) VALUES( '8')
INSERT INTO @alpha (c) VALUES( '9')
INSERT INTO @alpha (c) VALUES( 'a')
INSERT INTO @alpha (c) VALUES( 'b')
INSERT INTO @alpha (c) VALUES( 'c')
INSERT INTO @alpha (c) VALUES( 'd')
INSERT INTO @alpha (c) VALUES( 'e')
INSERT INTO @alpha (c) VALUES( 'f')
INSERT INTO @alpha (c) VALUES( 'g')
INSERT INTO @alpha (c) VALUES( 'h')
INSERT INTO @alpha (c) VALUES( 'i')
INSERT INTO @alpha (c) VALUES( 'j')
INSERT INTO @alpha (c) VALUES( 'k')
INSERT INTO @alpha (c) VALUES( 'l')
INSERT INTO @alpha (c) VALUES( 'm')
INSERT INTO @alpha (c) VALUES( 'n')
INSERT INTO @alpha (c) VALUES( 'o')
INSERT INTO @alpha (c) VALUES( 'p')
INSERT INTO @alpha (c) VALUES( 'q')
INSERT INTO @alpha (c) VALUES( 'r')
INSERT INTO @alpha (c) VALUES( 's')
INSERT INTO @alpha (c) VALUES( 't')
INSERT INTO @alpha (c) VALUES( 'u')
INSERT INTO @alpha (c) VALUES( 'v')
INSERT INTO @alpha (c) VALUES( 'w')
INSERT INTO @alpha (c) VALUES( 'x')
INSERT INTO @alpha (c) VALUES( 'y')
INSERT INTO @alpha (c) VALUES( 'z')

-- THIS IS THE START DATE FOR THE SYNCSTAMP
DECLARE @EPOCH AS datetime
SET @EPOCH = '12/31/1989 20:00'

DECLARE @X AS BIGINT
DECLARE @D AS BIGINT
SET @D = 0

-- SYNCSTAMP IS BASE 36 IN SECONDS FROM EPOCH DIVIDED BY 50
SELECT @X = val FROM @alpha WHERE C = SUBSTRING(@STR,7,1)
SET @D = @D + @X
SELECT @X = val FROM @alpha WHERE C = SUBSTRING(@STR,6,1)
SET @D = @D + @X*36
SELECT @X = val FROM @alpha WHERE C = SUBSTRING(@STR,5,1)
SET @D = @D + @X*POWER(36,2)
SELECT @X = val FROM @alpha WHERE C = SUBSTRING(@STR,4,1)
SET @D = @D + @X*POWER(36,3)
SELECT @X = val FROM @alpha WHERE C = SUBSTRING(@STR,3,1)
SET @D = @D + @X*POWER(36,4)
SELECT @X = val FROM @alpha WHERE C = SUBSTRING(@STR,2,1)
SET @D = @D + @X*POWER(36,5)
SELECT @X = val FROM @alpha WHERE C = SUBSTRING(@STR,1,1)
SET @D = @D + (@X*2176782336)

SET @D = @D/50

DECLARE @date datetime

SET @date =  DATEADD(s, @D, @EPOCH)


-- DETECT DAYLIGHT SAVINGS TIME AND ADJUST
declare @firstsunday datetime /* date of first sunday of April */
declare @lastsunday datetime /* date of last sunday of Oct */
set @firstsunday = convert(datetime,'1 Apr ' +
convert(char(4),(convert(int,datepart(year,@date))+1)) + ' 02:00:00')
while( datepart(dw,@firstsunday) != 1 ) begin
    set @firstsunday=dateadd(dd,1,@firstsunday)
end

set @lastsunday = convert(datetime,'31 Oct ' +
convert(char(4),datepart(year,@date)) + ' 02:00:00')
while( datepart(dw,@lastsunday) != 1 ) begin
    set @lastsunday=dateadd(dd,-1,@lastsunday)
end
-- if start/end date completely within daylight savings
 if ((@date < @firstsunday) and (@date >= @lastsunday)) begin
    set @date = DATEADD(hh,-1,@date)
  end

RETURN @date

END