sql 节假日判断（春节、中秋、国庆、周末等）代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 sql 节假日判断（春节、中秋、国庆、周末等）
作者:Dezai.CN / 发布于2013/5/12/ 770

	set ANSI_NULLS ON
	set QUOTED_IDENTIFIER ON
	go

	
	-- 日期检测函数，返回相关节假日
	-- 0 非假日
	-- 农历相关假日
	-- 1 春节(正月初一 至 正月初七)
	-- 2 端午节(五月五日)
	-- 4 中秋节(八月十五)

	--阳历相关节日
	-- 8 元旦(1月1日)
	-- 16 清明节(4月5日/闰年 4月6日)
	-- 32 劳动节(5月1日)
	-- 64 国庆节(10月1日)

	--128 周末

	ALTER FUNCTION [dbo].[fnCheckDate](@solarDay DATETIME)
	 RETURNS bigint AS
	 BEGIN
	 DECLARE @solData int
	 DECLARE @offset int
	 DECLARE @iLunar int
	 DECLARE @i INT
	 DECLARE @j INT
	 DECLARE @yDays int
	 DECLARE @mDays int
	 DECLARE @mLeap int
	 DECLARE @mLeapNum int
	 DECLARE @bLeap smallint
	 DECLARE @temp int
	
	 DECLARE @YEAR INT
	 DECLARE @MONTH INT
	 DECLARE @DAY INT
	
	 DECLARE @OUTPUTDATE varchar(100)

	 DECLARE @OUTPUTDATA Bigint --返回数值
	 SET @OUTPUTDATA = 0 --初始化为非假日
	
	 --保证传进来的日期是不带时间
	
	 SET @solarDay= convert(datetime,@solarDay,23)
	 SET @offset=CAST(@solarDay-'1900-01-30' AS INT)
	 print @solarDay
	 print @offset
	 return 0
	
	 --确定农历年开始
	 SET @i=1900
	 WHILE @i<2050 AND @offset>0
	 BEGIN
	 SET @yDays=348
	 SET @mLeapNum=0
	 SELECT @iLunar=dataInt FROM SolarData WHERE yearId=@i
	
	 --传回农历年的总天数
	 SET @j=32768
	 WHILE @j>8
	 BEGIN
	 IF @iLunar & @j >0
	 SET @yDays=@yDays+1
	 SET @j=@j/2
	 END
	
	 --传回农历年闰哪个月 1-12 , 没闰传回 0
	 SET @mLeap = @iLunar & 15
	
	 --传回农历年闰月的天数 ,加在年的总天数上
	 IF @mLeap > 0
	 BEGIN
	 IF @iLunar & 65536 > 0
	 SET @mLeapNum=30
	 ELSE
	 SET @mLeapNum=29
	
	 SET @yDays=@yDays+@mLeapNum
	 END
	
	 SET @offset=@offset-@yDays
	 SET @i=@i+1
	 END
	
	 IF @offset <=0
	 BEGIN
	 SET @offset=@offset+@yDays
	 SET @i=@i-1
	 END
	 --确定农历年结束
	 SET @YEAR=@i
	
	 --确定农历月开始
	 SET @i = 1
	 SELECT @iLunar=dataInt FROM SolarData WHERE yearId=@YEAR
	
	 --判断那个月是润月
	 SET @mLeap = @iLunar & 15
	 SET @bLeap = 0
	
	 WHILE @i < 13 AND @offset > 0
	 BEGIN
	 --判断润月
	 SET @mDays=0
	 IF (@mLeap > 0 AND @i = (@mLeap+1) AND @bLeap=0)
	 BEGIN--是润月
	 SET @i=@i-1
	 SET @bLeap=1
	 --传回农历年闰月的天数
	 IF @iLunar & 65536 > 0
	 SET @mDays = 30
	 ELSE
	 SET @mDays = 29
	 END
	 ELSE
	 --不是润月
	 BEGIN
	 SET @j=1
	 SET @temp = 65536
	 WHILE @j<=@i
	 BEGIN
	 SET @temp=@temp/2
	 SET @j=@j+1
	 END
	
	 IF @iLunar & @temp > 0
	 SET @mDays = 30
	 ELSE
	 SET @mDays = 29
	 END
	
	 --解除闰月
	 IF @bLeap=1 AND @i= (@mLeap+1)
	 SET @bLeap=0
	
	 SET @offset=@offset-@mDays
	 SET @i=@i+1
	 END
	
	 IF @offset <= 0
	 BEGIN
	 SET @offset=@offset+@mDays
	 SET @i=@i-1
	 END
	
	 --确定农历月结束
	 SET @MONTH=@i
	
	 --确定农历日结束
	 SET @DAY=@offset
	
	 IF @bLeap=1
	 SET @OUTPUTDATE=(CAST(@YEAR AS VARCHAR(4))+'-润'+CAST(@MONTH AS VARCHAR(2))+'-'+CAST(@DAY AS VARCHAR(2)))
	 ELSE
	 SET @OUTPUTDATE=(CAST(@YEAR AS VARCHAR(4))+CAST(@MONTH AS VARCHAR(2))+'-'+CAST(@DAY AS VARCHAR(2)))

	
	 DECLARE @tempStart NVARCHAR(20)
	 DECLARE @tempEnd NVARCHAR(20)
	 IF charindex('-润',@OUTPUTDATE) =0 -- 农历假期判断
	 Begin
	 -- 春节判断
	 DECLARE @preYear int
	 SET @preYear= YEAR(@solarDay)-1
	 IF(@preYear%4=0 AND (@preYear%100<>0 or (@preYear%100=0 and @preYear%400=0)) )
	 set @tempStart= Cast(@preYear AS VARCHAR(4)) +'12'+'29'
	 Else
	 set @tempStart= Cast(@preYear AS VARCHAR(4)) +'12'+'30'
	 set @tempEnd =Cast(YEAR(@solarDay) AS VARCHAR(4)) +'01'+'06'
	 IF(Convert(datetime,CONVERT(varchar(100), @OUTPUTDATE, 23)) >= @tempStart AND Convert(datetime,CONVERT(varchar(100), @OUTPUTDATE, 23)) <= @tempEnd)
	 SET @OUTPUTDATA =@OUTPUTDATA | 1
	 --端午节判断
	 set @tempStart= Cast(@preYear AS VARCHAR(4)) +'05'+'05'
	 IF(Convert(datetime,CONVERT(varchar(100), @OUTPUTDATE, 23)) = Convert(datetime,@tempStart))
	 SET @OUTPUTDATA =@OUTPUTDATA | 2
	 --中秋节
	 set @tempStart= Cast(@preYear AS VARCHAR(4)) +'08'+'15'
	 IF(Convert(datetime,CONVERT(varchar(100), @OUTPUTDATE, 23)) = Convert(datetime,@tempStart))
	 SET @OUTPUTDATA =@OUTPUTDATA | 4
	 End
	
	 -- 阳历假期判断
	 --元旦
	 DECLARE @CurrentYear int
	 SET @CurrentYear = YEAR(@solarDay)
	 set @tempStart= Cast(@CurrentYear AS VARCHAR(4)) +'01'+'01'
	 IF(Convert(datetime,CONVERT(varchar(100), @solarDay, 23)) = Convert(datetime,@tempStart))
	 SET @OUTPUTDATA =@OUTPUTDATA | 8
	 --清明节
	 IF(@CurrentYear%4=0 AND (@CurrentYear%100<>0 or (@CurrentYear%100=0 and @CurrentYear%400=0)))
	 set @tempStart= Cast(@CurrentYear AS VARCHAR(4)) +'04'+'04'
	 ELSE
	 set @tempStart= Cast(@CurrentYear AS VARCHAR(4)) +'04'+'05'
	 IF(Convert(datetime,CONVERT(varchar(100), @solarDay, 23)) = Convert(datetime,@tempStart))
	 SET @OUTPUTDATA =@OUTPUTDATA | 16
	 --五一
	 set @tempStart= Cast(@CurrentYear AS VARCHAR(4)) +'05'+'01'
	 IF(Convert(datetime,CONVERT(varchar(100), @solarDay, 23)) = Convert(datetime,@tempStart))
	 SET @OUTPUTDATA =@OUTPUTDATA | 32
	 --十一
	 set @tempStart= Cast(@CurrentYear AS VARCHAR(4)) +'10'+'01'
	 set @tempEnd =Cast(@CurrentYear AS VARCHAR(4)) +'10'+'03'
	 IF(Convert(datetime,CONVERT(varchar(100), @solarDay, 23)) >= @tempStart AND Convert(datetime,CONVERT(varchar(100), @solarDay, 23)) <= @tempEnd)
	 SET @OUTPUTDATA =@OUTPUTDATA | 64

	 -- 周末判断
	
	 IF((DATEPART(Weekday,@solarDay)+@@DATEFIRST-1)%7 =0 OR (DATEPART(Weekday,@solarDay)+@@DATEFIRST-1)%7 =6)
	 SET @OUTPUTDATA =@OUTPUTDATA | 128
	
	 RETURN @OUTPUTDATA
	 END
试试其它关键字
　节假日判断　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)