西方一些特殊节日的计算代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】西方一些特殊节日的计算
作者:Dezai.CN / 发布于2011/3/24/ 539
*
fcuandy
2011.3.23
this can be stored in file system or data table.
*/
DECLARE @x_SPEC XML
SET @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 @y=@year
 SET @hy=@y + 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 + <a href="mailto:5*@leap">5*@leap</a> + 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 @y=@year
 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)) * ((<a href="mailto:21-@met)/11">21-@met)/11</a>))
 SET @j = (@y + @y/4 + @i + 2 - @cent + @cent/4 ) % 7
 SET @emo = 3 + (@i-@j + 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 @y=@year
 SET @itXML = @x
 SET @itKey = @key
 
 SET @sql= @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
 EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD1 OUT
 
 SET @y = @y + 1
 EXEC sp_executeSQL @sql,N''@year 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 @y=@year
 SET @itXML = @x
 SET @itKey = @key
 
 SET @sql= @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
 EXEC sp_executeSQL @sql,N''@year 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 @y=@year
 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,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD1 OUT
 SET @y = @year
 EXEC sp_executeSQL @sql,N''@year 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 @y=@year
 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,N''@year 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 DATETIME
SET @year= 2010 --param from outside
/*
INSERT holidays SPEC
*/
DECLARE @i INT, @cnt INT
SELECT @i = 1, @cnt = @x_SPEC.value('count(//key)','INT')
WHILE @i<= @cnt
BEGIN
 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,N'@year INT,@outDay DATETIME OUT',@year,@hDay OUT
 ELSE
 EXEC sp_executeSQL @sql,N'@year 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 @i=@i+1
END
SELECT * FROM @t_res
试试其它关键字
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)