刚刚写的，提取了一部分。比如复活节等。
关于性能， 我也知道分开每个写一个function好的多，呵呵。
旨在重用、动态加载、程序结构上的一个设计思想。
- SQL code
/*fcuandy2011.3.23this can be stored in file system or data table.*/DECLARE @x_SPEC XMLSET @x_SPEC = '<root> <item> <id>1</id> <key>Passover</key> <refKey></refKey> <script> DECLARE @y INT ,@hy INT ,@mat INT ,@leap INT ,@fday NUMERIC(10,2) ,@ffday NUMERIC(10,2) ,@dow INT ,@cent INT ,@leapExp INT ,@day INT ,@mo INT ,@PassoverDay DATETIME SET @[email protected] SET @[email protected] + 3760 SET @mat = (12 * @hy + 17) %19 SET @leap = @hy %4 SET @fday = 32 + 4343 / 98496.0 + @mat + @mat * (272953 / 492480.0) + @leap / 4.0 SET @fday = @fday - @hy * (313 / 98496.0) SET @ffday = @fday - CAST(@fday AS INT) SET @dow = (3 * @hy + [email protected] + CAST(@fday AS INT) + 5) % 7 IF @dow IN (2,4,6) SET @fday = @fday + 1 ELSE IF @dow=1 AND @mat>6 AND @ffday>= 1367 / 2160.0 SET @fday = @fday + 2 ELSE IF @dow=0 AND @mat>11 AND @ffday>=23269 / 25920.0 SET @fday = @fday + 1 SET @cent = CAST(@y/100.0 AS INT) SET @leapExp = CAST((3 * @cent - 5)/4.0 AS INT) IF @y > 1582 SET @fday = @fday + @leapExp SET @day = CAST(@fday AS INT) SET @mo = 3 IF @day>153 BEGIN SET @mo = 8 SET @day = @day - 153 END ELSE IF @day>122 BEGIN SET @mo = 7 SET @day = @day -122 END ELSE IF @day > 92 BEGIN SET @mo = 6 SET @day = @day - 92 END ELSE IF @day > 61 BEGIN SET @mo = 5 SET @day = @day - 61 END ELSE IF @day > 31 BEGIN SET @mo = 4 SET @day = @day - 31 END SET @PassoverDay = RTRIM(@y) + ''-''+RTRIM(@mo) + ''-'' + RTRIM(@Day) SET @outDay = @PassoverDay </script> </item> <item> <id>2</id> <key>Easter</key> <refKey></refKey> <script> DECLARE @y INT ,@cent INT ,@i INT ,@j INT ,@k INT ,@met INT ,@emo INT ,@eday INT ,@Easter DATETIME SET @[email protected] SET @cent = @y /100 SET @met = @y % 19 SET @k = (@cent - 17) / 25 SET @i = (@cent - @cent / 4 - (@cent - @k)/3 + 19 * @met + 15)%30 SET @i = @i - (@i/28) * (1 - (@i/28) * (29/(@i+1)) * (([email protected])/11)) SET @j = (@y + @y/4 + @i + 2 - @cent + @cent/4 ) % 7 SET @emo = 3 + (@[email protected] + 40) / 44 SET @eday = @i - @j + 28 - 31 * (@emo / 4) SET @Easter = RTRIM(@y) + ''-''+RTRIM(@emo) + ''-'' + RTRIM(@eday) SET @outDay = @Easter </script> </item> <item> <id>3</id> <key>OEaster</key> <refKey></refKey> <script> DECLARE @y INT ,@i INT ,@j INT ,@met INT ,@emo INT ,@eday INT ,@leap INT ,@OEaster DATETIME SET @y = @year SET @leap = @y /100 - @y / 400 -2 SET @met = @y % 19 SET @i = (19 * @met + 15) % 30 SET @j = (@y + @y /4 + @i) % 7 SET @emo = 3 + (@i - @j + 40) / 44 SET @eday = @i - @j + 28 - 31 * (@emo /4) SET @OEaster = RTRIM(@y) + ''-''+RTRIM(@emo) + ''-'' + RTRIM(@eday) SET @outDay = @OEaster </script> </item> <item> <id>4</id> <key>Chanukah</key> <refKey>Passover</refKey> <script> DECLARE @y INT ,@days INT ,@PassoverD1 DATETIME ,@PassoverD2 DATETIME ,@Chanukah DATETIME ,@sql NVARCHAR(2000) ,@itXml XML ,@itKey VARCHAR(40) SET @[email protected] SET @itXML = @x SET @itKey = @key SET @sql= @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'') EXEC sp_executeSQL @sql,[email protected] INT,@outDay DATETIME OUT'',@y,@PassoverD1 OUT SET @y = @y + 1 EXEC sp_executeSQL @sql,[email protected] INT,@outDay DATETIME OUT'',@y,@PassoverD2 OUT SET @days = DATEDIFF(dd,@PassoverD1,@PassoverD2) IF @days IN (355, 385) SET @outDay = DATEADD(dd,246,@PassoverD1) ELSE SET @outDay = DATEADD(dd,245,@PassoverD1) </script> </item> <item> <id>5</id> <key>TishaBAv</key> <refKey>Passover</refKey> <script> DECLARE @y INT ,@dw INT ,@Passover DATETIME ,@sql NVARCHAR(2000) ,@itXml XML ,@itKey VARCHAR(40) SET @[email protected] SET @itXML = @x SET @itKey = @key SET @sql= @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'') EXEC sp_executeSQL @sql,[email protected] INT,@outDay DATETIME OUT'',@y,@Passover OUT SET @dw = DATEPART(DW,@Passover) IF @dw = 7 SET @outDay = DATEADD(dd,113, @Passover) ELSE SET @outDay = DATEADD(dd,112, @Passover) </script> </item> <item> <id>6</id> <key>TuBishvat</key> <refKey>Passover</refKey> <script> DECLARE @y INT ,@days INT ,@PassoverD1 DATETIME ,@PassoverD2 DATETIME ,@sql NVARCHAR(2000) ,@itXml XML ,@itKey VARCHAR(40) SET @[email protected] SET @itXML = @x SET @itKey = @key SET @y = @y - 1 SET @sql= @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'') EXEC sp_executeSQL @sql,[email protected] INT,@outDay DATETIME OUT'',@y,@PassoverD1 OUT SET @y = @year EXEC sp_executeSQL @sql,[email protected] INT,@outDay DATETIME OUT'',@y,@PassoverD2 OUT SET @days = DATEDIFF(dd,@PassoverD1,@PassoverD2) IF @days > 355 SET @outDay = DATEADD(dd,-89, @PassoverD2) ELSE SET @outDay = DATEADD(dd,-59, @PassoverD2) </script> </item> <item> <id>7</id> <key>YomHaAtzmaut</key> <refKey>Passover</refKey> <script> DECLARE @y INT ,@dw INT ,@Passover DATETIME ,@sql NVARCHAR(2000) ,@itXml XML ,@itKey VARCHAR(40) SET @[email protected] SET @itXML = @x SET @itKey = @key IF @y = 2004 SET @outDay = CONVERT(DATETIME,''2004-04-27'',120) ELSE BEGIN SET @sql= @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'') EXEC sp_executeSQL @sql,[email protected] INT,@outDay DATETIME OUT'',@y,@Passover OUT SET @dw = DATEPART(DW,@Passover) IF @dw = 1 SET @outDay = DATEADD(dd,18,@Passover) ELSE IF @dw = 7 SET @outDay = DATEADD(dd,19,@Passover) ELSE SET @outDay = DATEADD(dd,20,@Passover) END </script> </item></root> 'DECLARE @t_res TABLE( hType VARCHAR, --F,M,S,O yy INT, hKey VARCHAR(40), hDate DATETIME, wDay AS DATEPART(DW,hDate) )DECLARE @key VARCHAR(40) ,@refKey VARCHAR(40) ,@year INT ,@sql NVARCHAR(2000) ,@hDay DATETIMESET @year= 2010 --param from outside/*INSERT holidays SPEC*/DECLARE @i INT, @cnt INTSELECT @i = 1, @cnt = @x_SPEC.value('count(//key)','INT')WHILE @i<= @cntBEGIN SELECT @sql=T.x.value('script[1]','NVARCHAR(2000)') , @key = T.x.value('key[1]','VARCHAR(40)'), @refKey = T.x.value('refKey[1]','VARCHAR(40)') /*FROM @x_SPEC.nodes('root/item[position()=sql:variable("@i")]') AS T(x)*/ FROM @x_SPEC.nodes('root/item[id=sql:variable("@i")]') AS T(x) IF @refKey = '' EXEC sp_executeSQL @sql,[email protected] INT,@outDay DATETIME OUT',@year,@hDay OUT ELSE EXEC sp_executeSQL @sql,[email protected] INT,@x XML,@key VARCHAR(40),@outDay DATETIME OUT',@year,@x_SPEC,@refKey,@hDay OUT INSERT @t_res(hType,yy,hKey,hDate) SELECT 'S',@year,@key,@hDay SET @[email protected]+1ENDSELECT * FROM @t_res