SQL取出 所有周六 周日的日期
createtable SatSun([id]intidentity(1,1),[date]datetime,[weekday]char(6))
go
declare@datetimedatetime,@weekdaychar(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
droptable 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@iint
declare@ddatetime
set@i=1
set@d='2007-1-1'
Createtable #a (Dat datetime)
whileyear(@d)=2007
begin
insertinto #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) =1ordatepart(dw,Dat) =7
****************************************************************************
/*
功能: 计算在某一段时间内某周几（如星期一）的所有日期
设计：OK_008
时间：2006-10
*/
DECLARE@Datedatetime
DECLARE@StartDatedatetime
DECLARE@EndDatedatetime
DECLARE@WeekDayint
DECLARE@iint
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(@@DATEFIRSTASnvarchar(1))
PRINT'开始日期对应一周的第几天: '+CAST(@iASnvarchar(1))
IF(@i<=@WeekDayAND@i<7)
SET@i=@WeekDay-@i
ELSEIF(@i<=@WeekDayAND@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) PRINTCONVERT(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
..........
*/
selectdateadd(day,x,col),'星期二'from
(
selectcast('2006-1-1'asdatetime) as col
)a crossjoin
(
SELECTtop365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
FROM(SELECT0 i UNIONALLSELECT1) b0
CROSSJOIN(SELECT0 i UNIONALLSELECT2) b1
CROSSJOIN(SELECT0 i UNIONALLSELECT4) b2
CROSSJOIN(SELECT0 i UNIONALLSELECT8) b3
CROSSJOIN(SELECT0 i UNIONALLSELECT16) b4
CROSSJOIN(SELECT0 i UNIONALLSELECT32) b5
CROSSJOIN(SELECT0 i UNIONALLSELECT64) b6
CROSSJOIN(SELECT0 i UNIONALLSELECT128) b7
CROSSJOIN(SELECT0 i UNIONALLSELECT256) b8
orderby1
)b
wheredatepart(dw,dateadd(day,x,col))=3 (这个地方改值换取其他日期)
2006-01-0300:00:00.000 星期二
2006-01-1000:00:00.000 星期二
2006-01-1700:00:00.000 星期二
2006-01-2400:00:00.000 星期二
2006-01-3100: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)
****************************************************************************
go
declare@datetimedatetime,@weekdaychar(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
droptable 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@iint
declare@ddatetime
set@i=1
set@d='2007-1-1'
Createtable #a (Dat datetime)
whileyear(@d)=2007
begin
insertinto #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) =1ordatepart(dw,Dat) =7
****************************************************************************
/*
功能: 计算在某一段时间内某周几（如星期一）的所有日期
设计：OK_008
时间：2006-10
*/
DECLARE@Datedatetime
DECLARE@StartDatedatetime
DECLARE@EndDatedatetime
DECLARE@WeekDayint
DECLARE@iint
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(@@DATEFIRSTASnvarchar(1))
PRINT'开始日期对应一周的第几天: '+CAST(@iASnvarchar(1))
IF(@i<=@WeekDayAND@i<7)
SET@i=@WeekDay-@i
ELSEIF(@i<=@WeekDayAND@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) PRINTCONVERT(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
..........
*/
selectdateadd(day,x,col),'星期二'from
(
selectcast('2006-1-1'asdatetime) as col
)a crossjoin
(
SELECTtop365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
FROM(SELECT0 i UNIONALLSELECT1) b0
CROSSJOIN(SELECT0 i UNIONALLSELECT2) b1
CROSSJOIN(SELECT0 i UNIONALLSELECT4) b2
CROSSJOIN(SELECT0 i UNIONALLSELECT8) b3
CROSSJOIN(SELECT0 i UNIONALLSELECT16) b4
CROSSJOIN(SELECT0 i UNIONALLSELECT32) b5
CROSSJOIN(SELECT0 i UNIONALLSELECT64) b6
CROSSJOIN(SELECT0 i UNIONALLSELECT128) b7
CROSSJOIN(SELECT0 i UNIONALLSELECT256) b8
orderby1
)b
wheredatepart(dw,dateadd(day,x,col))=3 (这个地方改值换取其他日期)
2006-01-0300:00:00.000 星期二
2006-01-1000:00:00.000 星期二
2006-01-1700:00:00.000 星期二
2006-01-2400:00:00.000 星期二
2006-01-3100: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
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
