已知开始日期和结束日期: 2015-11-01 ,2015-11-30,获得时间范围内的周六和周日的日期
返回日期和星期几两列
2015-11-01,日
2015-11-07,六
2015-11-08,日
2015-11-14,六
2015-11-15,日
2015-11-21,六
2015-11-22,日
2015-11-28,六
-- SQL如下
方法1)
Select
dt,
case
when
DATEPART(dw,dt)=1
then
'日'
else
'六'
end
from
(
select
convert
(datetime,
'2015-11-01'
)+number dt
from
master..spt_values
where
type=
'P'
and
convert
(datetime,
'2015-11-01'
)+number<=
CONVERT
(datetime,
'2015-11-30'
)
)aa
where
DATEPART(dw,dt)
in
(1,7)
/*-----------------------------------------------------------------------------------*/
方法2)
declare
@sdate datetime,@edate datetime,@i
int
,@j
int
,@k
int
set
@sdate=
'2015-11-01'
set
@edate=
'2015-11-30'
set
@i =0
select
@j=DATEDIFF(DD,@sdate,@edate)
create
table
#tab
(
[
date
] datetime,
[week]
varchar
(4)
)
while (@i<= @j)
begin
select
@k= datepart(weekday, DATEADD(DD,@i,@sdate))
if(@k=1
or
@k=7)
begin
insert
into
#tab
select
DATEADD(DD,@i,@sdate),
case
when
datepart(weekday, DATEADD(DD,@i,@sdate))=1
then
'日'
else
'六'
end
end
set
@i=@i+1
end
select
convert
(
varchar
(20),[
date
],23)
as
[
date
],[week]
from
#tab
drop
table
#tab