- 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
sql 节假日判断(春节、中秋、国庆、周末等)
最新推荐文章于 2023-11-09 17:20:20 发布