MSSQLSERVER2000中文:
select count(*) from tablename where datename(dw,datetime_col) not in ('星期六','星期日') and month(datetime_col)=2
select count(*) from tablename where datename(dw,datetime_col) not in ('星期六','星期日') and month(datetime_col)=2
调试欢乐多
谢了,不过我要的是系统中的,不是在表里的。
比如当前日期是2002-04-26,我要的是在2002-04中有几天不是星期六星期天。
declare @beginday datetime,@endday datetime,@caldays int
select @beginday='2002-03-01'
select @endday='2002-04-01'
select @caldays=0
while @beginday<@endday
begin
if datepart(dw,@beginday)>5
select @caldays=@caldays+1
select @beginday=dateadd(day,1,@beginday)
end
print @caldays你可以把上面的SQL语句写成存储过程,其中@beginday,@endday为传入参数,@caldays为传出参数
if datepart(dw,@beginday)>5 改成
if datepart(dw,@beginday)<6就可以了
CREATE FUNCTION Workdays
(
@now char(6)
)
returns int
AS
begin
if isdate(@now+'01')=0
return 0
declare @days int,@workdays int,@day int
set @days=case when isdate(@now+'31')=1 then 31
when isdate(@now+'30')=1 then 30
when isdate(@now+'29')=1 then 29
when isdate(@now+'28')=1 then 28 end
select @workdays=0,@day=1
while @day<=@days
select @workdays=case when datepart(dw,@now+case when @day<10 then '0' else '' end +cast(@day as varchar(2)))<6 then 1 else 0 end+@workdays,
@day=@day+1
return @workdays
end
---------------------------------------------------
执行:
set datefirst 1
select dbo.Workdays('200202')