mysql 节假日判断,sql 节假日判断（春节、中秋、国庆、周末等）

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