如何求每个月星期六星期天的天数
当月工作天数=当月天数 - 当月星期六天数 - 当月星期天天数
请问如何求每个月星期六星期天的天数
[解决办法]
--生成當月所有日期
select top 31 id = identity(int, 1, 1), dt = left(convert(varchar(10), getdate(), 120), 8) + '01 '
into #t
from syscolumns, sysobjects
update #t
set dt = (select convert(varchar(10), dateadd(day, id - 1, dt), 120) from #t where id = t.id)
from #t t
--查詢當月星期六,星期天的天數之和
select count(1) from #t
where datepart(dw, dt) in(1, 7) and datediff(month, getdate(), dt) = 0
drop table #t
[解决办法]
create proc GetDateNum
@sdate smalldatetime
as
declare @edate smalldatetime
set @edate=dateadd(day,-1,dateadd(month,1,@sdate))
select top 31 id=identity(int,0,1) into # from syscolumns
select count(1) 总数 from (
select dateadd(day,id,@sdate) g,datepart(weekday,dateadd(day,id,@sdate)) a from # where dateadd(day,id,@sdate) <=@edate
)aa where a=1 or a=7
drop table #
go
exec GetDateNum '2007-07-01 '
[解决办法]
--输入某月的第一天，得到这个月的星期六、星期日的天数合计
CREATE FUNCTION DBO.SUM_MONTH_REST_DATE(@YYYYMMDD VARCHAR(8))
RETURNS INT
AS
BEGIN
DECLARE @SUM_REST_DATE INT
DECLARE @_YYYYMMDD VARCHAR(8)
SET @SUM_REST_DATE=0
SET @_YYYYMMDD=@YYYYMMDD
WHILE LEFT(@_YYYYMMDD,6)=LEFT(@YYYYMMDD,6)
BEGIN
IF DATEPART(WEEKDAY,@_YYYYMMDD)=1 OR DATEPART(WEEKDAY,@_YYYYMMDD)=7
SET @SUM_REST_DATE=@SUM_REST_DATE+1
SET @_YYYYMMDD=CONVERT(VARCHAR(8),DATEADD(D,1,@_YYYYMMDD),112)
END
RETURN @SUM_REST_DATE
END
--测试：
SELECT DBO.SUM_MONTH_REST_DATE( '20070701 ')
--结果：
-----------
9
（所影响的行数为 1 行）
[解决办法]
declare @int_month int,@max_day int,@strDate varchar(10)
set @int_month=7 --输入月份
if @int_month between 1 and 12
begin
select @strDate=cast(year(getdate()) as varchar(10))+ '- '+cast(@int_month+1 as varchar(10))+ '- '+cast( '1 ' as varchar)
select @max_day=day(dateadd(day,-1,convert(varchar(10),@strDate,120)))
declare @t table(sdate smalldatetime)
declare @i int
set @i=1
while(@i <=@max_day)
begin
insert into @t select cast(year(getdate()) as varchar(10))+ '- '+cast(@int_month as varchar(10))+ '- '+cast(@i as varchar)
set @i=@i+1
end
select count(1) from @t where datepart(dw,sdate)-1 not in (0,6)
end
else
print '月份错误 '
