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

代码语言
代码分类
【MSSQL】取出所有周六周日的日期
作者:Dezai.CN / 发布于2012/12/12/ 774
create 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                                星期六
……
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

Original text:http://www.cnblogs.com/wayne-ivan/archive/2008/04/10/1146308.html
  评论这张 
 转发至微博 
 转发至
试试其它关键字
　日期　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)