SQL取出所有周六周日的日期代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL取出所有周六周日的日期
作者:Dezai.CN / 发布于2013/6/12/ 769
SQL取出所有周六周日的日期

	reate table SatSun([id] int identity(1,1),[date] datetime,[weekday] char(6))
	go

	declare @datetime datetime,@weekday char(6)
	set @datetime='2007-1-1'
	while @datetime<='2007-12-31'
	begin
	select @weekday=datename(weekday,@datetime)
	if @weekday= '星期六' insert SatSun([date],[weekday]) values(@datetime,@weekday)
	if @weekday='星期日' insert SatSun([date],[weekday]) values(@datetime,'星期天')
	select @datetime=dateadd(day,1,@datetime)
	end
	go

	select * from satsun

	drop table satsun

	/*
	id date weekday
	----------- ------------------------------------------------------ -------
	1 2007-01-06 00:00:00.000 星期六
	2 2007-01-07 00:00:00.000 星期天
	3 2007-01-13 00:00:00.000 星期六
	4 2007-01-14 00:00:00.000 星期天
	5 2007-01-20 00:00:00.000 星期六
	6 2007-01-21 00:00:00.000 星期天
	7 2007-01-27 00:00:00.000 星期六
	8 2007-01-28 00:00:00.000 星期天
	9 2007-02-03 00:00:00.000 星期六
	&hellip;&hellip;
	100 2007-12-16 00:00:00.000 星期天
	101 2007-12-22 00:00:00.000 星期六
	102 2007-12-23 00:00:00.000 星期天
	103 2007-12-29 00:00:00.000 星期六
	104 2007-12-30 00:00:00.000 星期天

	*/（所影响的行数为 104 行）

	
	****************************************************************************

	declare @t table(dd datetime)
	declare @d1 datetime
	select @d1='2007-1-1'
	while(year(@d1) <2008)
	begin
	insert into @t select @d1
	set @d1=dateadd(dd,1,@d1)
	end

	select * from @t where datepart(weekday,dd)=6 or datepart(weekday,dd)=7

	
	****************************************************************************
	declare @i int
	declare @d datetime
	set @i=1
	set @d='2007-1-1'
	Create table #a (Dat datetime)
	while year(@d)=2007
	 begin
	 insert into #a values (@d )
	 set @i=@i+1
	 set @d=dateadd(day,1,@d)
	 end
	select *,datepart(dw,Dat) as a from #a where datepart(dw,Dat) =1 or datepart(dw,Dat) =7

	****************************************************************************
	/*
	功能: 计算在某一段时间内某周几（如星期一）的所有日期
	设计：OK_008
	时间：2006-10
	*/
	DECLARE @Date datetime
	DECLARE @StartDate datetime
	DECLARE @EndDate datetime
	DECLARE @WeekDay int
	DECLARE @i int

	SET DATEFIRST 7 --设置每周的第一天
	SET @StartDate='2006-01-01' --统计的开始日期
	SET @EndDate='2006-12-31' --统计的结束日期
	SET @WeekDay=1 --根据实际的@@DATEFIRST而定，一般默认是7，如 @StartDate='2006-01-01'时候， @WeekDay=3表示星期二
	SET @i=DATEPART(weekday,@StartDate)
	PRINT '每周的第1天设置@@DATEFIRST: '+CAST(@@DATEFIRST AS nvarchar(1))
	PRINT '开始日期对应一周的第几天: '+CAST(@i AS nvarchar(1))
	IF(@i<=@WeekDay AND @i<7)
	SET @i=@WeekDay-@i
	ELSE IF(@i<=@WeekDay AND @i=7)
	SET @i=@i-@WeekDay
	ELSE
	SET @i=@@DATEFIRST-@i+@WeekDay

	SET @Date=DATEADD(day,@i,@StartDate)
	WHILE @Date<=@EndDate
	BEGIN
	IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121)
	SET @Date=DATEADD(Week,1,@Date)
	END
	GO

	/* ==============运行结果================*/
	/*
	每周的第1天设置@@DATEFIRST: 7
	开始日期对应一周的第几天: 1
	2006-01-01
	2006-01-08
	2006-01-15
	2006-01-22
	2006-01-29
	..........
	*/

	

	select dateadd(day,x,col),'星期二' from
	(
	select cast('2006-1-1' as datetime) as col
	)a cross join
	(
	SELECT top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
	FROM(SELECT 0 i UNION ALL SELECT 1) b0
	CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
	CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
	CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
	CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
	CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
	CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6
	CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7
	CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8
	order by 1
	)b
	where datepart(dw,dateadd(day,x,col))=3 (这个地方改值换取其他日期)

	2006-01-03 00:00:00.000 星期二
	2006-01-10 00:00:00.000 星期二
	2006-01-17 00:00:00.000 星期二
	2006-01-24 00:00:00.000 星期二
	2006-01-31 00:00:00.000 星期二

	****************************************************************************
	DECLARE @t TABLE(date0 DATETIME)
	DECLARE @st DATETIME,@et DATETIME
	SET @st='2007-01-01'
	SET @et='2008-01-01'
	WHILE @st <@et
	BEGIN
	INSERT INTO @t VALUES(@st)
	SELECT @st=DATEADD(DAY,1,@st)
	END
	SELECT date0,DATENAME(weekday,date0) FROM @t WHERE DATEPART(weekday,date0+@@DATEFIRST-1) IN (6,7)

	
	****************************************************************************

	由于工作需要，在SQL Server 2005 下面写了一个计算两个日期之间相差工作日的函数。函数是以一个星期5天工作日计算，没有剔除五一国庆等假期。代码如下：
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	-- =============================================
	-- Author: Sinmen
	-- Create date: 2007-11-01
	-- Description: 计算两个日期之间相差的工作日
	-- =============================================
	ALTER FUNCTION [dbo].[WorkDatediff]
	(
	@begin_date datetime,
	@end_date datetime
	)
	RETURNS int
	AS
	BEGIN
	DECLARE @return_date_quantity int
	DECLARE @temp datetime
	DECLARE @week_quantity int
	DECLARE @day_quantity int
	DECLARE @begin_day_of_week int
	DECLARE @end_day_of_week int
	DECLARE @add_begin_day_quantity int
	DECLARE @add_end_day_quantity int
	set @day_quantity = Datediff(d,@begin_date,@end_date)
	--判断传入的开始日期是否比结束日期大
	if @day_quantity < 0
	 begin
	 set @temp = @begin_date
	 set @begin_date = @end_date
	 set @end_date = @temp
	 end
	set @week_quantity = Abs(Datediff(ww,@begin_date,@end_date)) - 1
	if @week_quantity < 0
	 set @week_quantity = 0
	set @begin_day_of_week = Datepart(dw,@begin_date) - 1
	set @end_day_of_week = Datepart(dw,@end_date) - 1

	set @add_begin_day_quantity = case
	 when @begin_day_of_week > 5 then 0
	 else 6 - @begin_day_of_week --(5 - @begin_day_of_week + 1)
	 end
	set @add_end_day_quantity = case
	 when abs(@day_quantity) < 8 then 0
	 when @end_day_of_week > 5 then 5
	 else @end_day_of_week
	 end
	if @day_quantity = 0
	 set @return_date_quantity = 0
	else
	 set @return_date_quantity = @week_quantity * 5 + @add_begin_day_quantity + @add_end_day_quantity
	if @day_quantity < 0
	 set @return_date_quantity = @return_date_quantity * -1

	RETURN @return_date_quantity
	END
	GO
	SET ANSI_NULLS OFF
	GO
	SET QUOTED_IDENTIFIER OFF
	GO
试试其它关键字
　周六　　周日　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)