--在查询分析器中执行create table #t(日期 datetime,出勤 bit)declare @年 int, @月 intset @年=2006 set @月=5insert into #t(日期,出勤) values('2006-04-30',1) insert into #t(日期,出勤) values('2006-05-01',1) insert into #t(日期,出勤) values('2006-05-02',0) insert into #t(日期,出勤) values('2006-05-03',1)select *,datename(weekday,日期) as 星期 from #t where year(日期)=@年 and month(日期)=@月drop table #t
create table #table(日期 smalldatetime,出勤 bit) insert into #table select '20060401',1 union all select '20060402',0 union all select '20060501',0 union all select '20060502',0 union all select '20060503',1 union all select '20060504',1 godeclare @year int, @month int set @year = 2006 set @month = 5select *,datepart(wk,日期) as 星期 from #table where year(日期) = @year and month(日期) = @month--drop table #table
create table #table(日期 smalldatetime,出勤 bit) insert into #table select '20060401',1 union all select '20060402',0 union all select '20060501',0 union all select '20060502',0 union all select '20060503',1 union all select '20060504',1 godeclare @year int, @month int set @year = 2006 set @month = 5select *,datepart(wk,日期) as 星期 from #table where year(日期) = @year and month(日期) = @month--drop table #table这个显示的星期还是有问题? 按上面的输入 星期全是18 ????
set @月=5insert into #t(日期,出勤) values('2006-04-30',1)
insert into #t(日期,出勤) values('2006-05-01',1)
insert into #t(日期,出勤) values('2006-05-02',0)
insert into #t(日期,出勤) values('2006-05-03',1)select *,datename(weekday,日期) as 星期
from #t
where year(日期)=@年 and month(日期)=@月drop table #t
insert into #table select '20060401',1
union all select '20060402',0
union all select '20060501',0
union all select '20060502',0
union all select '20060503',1
union all select '20060504',1
godeclare @year int, @month int
set @year = 2006
set @month = 5select *,datepart(wk,日期) as 星期 from #table where year(日期) = @year and month(日期) = @month--drop table #table
insert into #table select '20060401',1
union all select '20060402',0
union all select '20060501',0
union all select '20060502',0
union all select '20060503',1
union all select '20060504',1
godeclare @year int, @month int
set @year = 2006
set @month = 5select *,datepart(wk,日期) as 星期 from #table where year(日期) = @year and month(日期) = @month--drop table #table这个显示的星期还是有问题?
按上面的输入
星期全是18
????
datepart(weekday,日期)就可以了,函数datepart(wk,日期)是查看该日期所在星期是该年的第几个星期;而datepart(weekday,日期)才是你想要的。注意函数datepart(weekday,日期)返回值中,星期天返回1,星期一返回2....所以如果你要和我们日常的习惯一致的话,在返回值基础上减去1,如datepart(weekday,日期)-1