请问如何用时间函数实现某两年2月份总共有多少天？

[tired_bird]

请问如何用时间函数实现某两年间2月份总共有多少天？

例如实现1980年到2005年间2月份总共多少天？

[liangCK]

select top 1000 id=identity(int,1,1) into # from syscolumns a,syscolumns b

select 共有天数=sum(days) from
(
    select days=datepart(day,dateadd(day,-1,convert(char(8),dateadd(month,1,[date]),120)+'1'))
    from
    (
        select [date]=dateadd(year,id,'1980-02-01')
        from #
        where dateadd(year,id,'1980-02-01')<='2005-02-01'
    ) b
) a

drop table #

[中国风]

create function F_year(@startDate int,@EndDate int)
returns int
as
begin
declare @MonthDay int
set @MonthDay=0
while @startDate!>@EndDate
	select @MonthDay=@MonthDay+datediff(dd,rtrim(@startDate)+'0201',dateadd(month,1,rtrim(@startDate)+'0201')),
	@startDate=@startDate+1
return @MonthDay
end
--
select dbo.F_year(2006,2007)

[liangCK]

select top 1000 id=identity(int,1,1) into # from syscolumns a,syscolumns b

select 共有天数=sum(days) from
(
    select days=datepart(day,dateadd(day,-1,convert(char(8),dateadd(month,1,[date]),120)+'1'))
    from
    (
        select [date]=dateadd(year,id,'1980-02-01')
        from #
        where dateadd(year,id,'1980-02-01')<='2005-02-01'
    ) b
) a

drop table #

/*
共有天数        
----------- 
706

（所影响的行数为 1 行）
*/

[中国风]

--只计算二月份的天数和
select dbo.F_year(1980,2007)
            
----------- 
791

（所影响的行数为 1 行）

[liangCK]

我上面错了点.现在正确了.

select top 1000 id=identity(int,0,1) into # from syscolumns a,syscolumns b

select 共有天数=sum(days) from
(
    select days=datepart(day,dateadd(day,-1,convert(char(8),dateadd(month,1,[date]),120)+'1'))
    from
    (
        select [date]=dateadd(year,id,'1980-02-01')
        from #
        where dateadd(year,id,'1980-02-01')<='2005-02-01'
    ) b
) a

drop table #

/*
共有天数        
----------- 
735

（所影响的行数为 1 行）
*/

[xinleile]

select sum(num) as [1980-2005 二月份天数] from (
select day(dateadd(dd,-1,'1980-03-01')) as num union all

select day(dateadd(dd,-1,'1981-03-01')) as num union all

select day(dateadd(dd,-1,'1982-03-01')) as num union all

select day(dateadd(dd,-1,'1983-03-01')) as num union all

select day(dateadd(dd,-1,'1984-03-01')) as num union all

select day(dateadd(dd,-1,'1985-03-01')) as num union all

select day(dateadd(dd,-1,'1986-03-01')) as num union all

select day(dateadd(dd,-1,'1987-03-01')) as num union all

select day(dateadd(dd,-1,'1988-03-01')) as num union all

select day(dateadd(dd,-1,'1989-03-01')) as num union all

select day(dateadd(dd,-1,'1990-03-01')) as num union all

select day(dateadd(dd,-1,'1991-03-01')) as num union all

select day(dateadd(dd,-1,'1992-03-01')) as num union all

select day(dateadd(dd,-1,'1993-03-01')) as num union all

select day(dateadd(dd,-1,'1994-03-01')) as num union all

select day(dateadd(dd,-1,'1995-03-01')) as num union all

select day(dateadd(dd,-1,'1996-03-01')) as num union all

select day(dateadd(dd,-1,'1997-03-01')) as num union all

select day(dateadd(dd,-1,'1998-03-01')) as num union all

select day(dateadd(dd,-1,'1999-03-01')) as num union all

select day(dateadd(dd,-1,'2000-03-01')) as num union all

select day(dateadd(dd,-1,'2001-03-01')) as num union all

select day(dateadd(dd,-1,'2002-03-01')) as num union all

select day(dateadd(dd,-1,'2003-03-01')) as num union all

select day(dateadd(dd,-1,'2004-03-01')) as num union all

select day(dateadd(dd,-1,'2005-03-01')) as num ) dd


/*
1980-2005 二月份天数
-----------------------------------
735

*/

[liangCK]

楼上..你累了吧.

[tired_bird]

先谢谢大家的帮忙！

但如果把1980　和　2005　换成变量＠yyear1 和 ＠yyear2　呢？
即实现＠yyear1到yyear2年间2月份总共多少天？能实现吗？感谢！　

[liangCK]

那你使用roy_88的函数就可以了啊.

[tired_bird]

谢谢，刚才没看到roy_88的函数！

[liangCK]

那给分结帖吧.

[-狙击手-]

接分

[liangCK]

BS技术帖接分..

[JL99000]

方法多多
不再写了
主要是个遍历查询的过程

[tired_bird]

在运行roy_88的F＿year函数即
create function F_year(@startDate int,@EndDate int)
returns int
as
begin
declare @MonthDay int
set @MonthDay=0
while @startDate!>@EndDate
select @MonthDay=@MonthDay+datediff(dd,rtrim(@startDate)+'0201',dateadd(month,1,rtrim(@startDate)+'0201')),
@startDate=@startDate+1
return @MonthDay
end

能成功，但在执行　select dbo.F_year(2006,2007)
语句时，却处于一直查询状态，不出结果，为什么，我可能哪步出错了

[中国风]

没遇到楼主说的情况，这样试试..

create   function   F_year(@startDate   nvarchar(4),@EndDate   nvarchar(4)) 
returns   int 
as 
begin 
	declare   @MonthDay   int 
	set   @MonthDay=0 
	while   @startDate!> @EndDate 
	        select   @MonthDay=@MonthDay+
				datediff(dd,
						cast(@startDate+'0201' as datetime),
						cast(Dateadd(month,1,@startDate+'0201') as datetime)
						) , 
	        @startDate=@startDate+1 
	return   @MonthDay 
end 

go
select   dbo.F_year(2006,2007) 

--drop function F_year

[dawugui]

CREATE PROCEDURE my_proc(@syear int , @eyear int)
as 
begin
  declare @i as int
  set @i = 0
  while @syear <= @eyear
  begin
      set @i = @i + datediff(day , cast(@syear as varchar) + '-02-01' , cast(@syear as varchar) + '-03-01')
      set @syear = @syear + 1
  end
  print 'result = ' + cast(@i as varchar)
end
go

exec my_proc 1980 , 2005
drop procedure my_proc

/*
result = 735
*/

[dawugui]

CREATE PROCEDURE my_proc(@syear int , @eyear int)
as 
begin
  declare @i as int
  set @i = 0
  while @syear <= @eyear
  begin
      set @i = @i + datediff(day , cast(@syear as varchar) + '-02-01' , cast(@syear as varchar) + '-03-01')
      set @syear = @syear + 1
  end
  print 'result = ' + cast(@i as varchar)
end
go

exec my_proc 1980 , 2005
drop procedure my_proc

/*
result = 735
*/

[dobear_0922]

全部按28天算，再加上中间闰年次数，，，