select * from tb where datepart(weekday,列名) not in (7,1) --节日肯定要自己造表的我个人估计,可以百度找找有没现成的
能否可以提供一下其它节日的明细表,谢谢..求SQL语句!!!!
看邹老大的BLOG 上面有节假日自定义函数
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
--传回农历年闰月的天数 ,加在年的总天数上 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
周末: select case when datepart(dw,dt) in (6,7) when dateadd(d,8-datepart(dw,dt),dt) else dt end) from tb 节日,另建一张表,存放节日日期,再与所查表连接处理.
其他节日应该加个节日来预存
select * from tb where datepart(weekday,列名) not in (7,1)
--节日肯定要自己造表的我个人估计,可以百度找找有没现成的
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
select case when datepart(dw,dt) in (6,7) when dateadd(d,8-datepart(dw,dt),dt) else dt end) from tb
节日,另建一张表,存放节日日期,再与所查表连接处理.