select top 1000 id=identity(int,1,1) into # from syscolumns a,syscolumns bselect 共有天数=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 ) adrop 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)
select top 1000 id=identity(int,1,1) into # from syscolumns a,syscolumns bselect 共有天数=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 ) adrop table #/* 共有天数 ----------- 706(所影响的行数为 1 行) */
--只计算二月份的天数和 select dbo.F_year(1980,2007)
----------- 791(所影响的行数为 1 行)
我上面错了点.现在正确了. select top 1000 id=identity(int,0,1) into # from syscolumns a,syscolumns bselect 共有天数=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 ) adrop table #/* 共有天数 ----------- 735(所影响的行数为 1 行) */
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*/
在运行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
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 goexec my_proc 1980 , 2005 drop procedure my_proc/* result = 735 */
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 goexec my_proc 1980 , 2005 drop procedure my_proc/* result = 735 */
select top 1000 id=identity(int,1,1) into # from syscolumns a,syscolumns bselect 共有天数=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
) adrop table #
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)
(
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
) adrop table #/*
共有天数
-----------
706(所影响的行数为 1 行)
*/
--只计算二月份的天数和
select dbo.F_year(1980,2007)
-----------
791(所影响的行数为 1 行)
select top 1000 id=identity(int,0,1) into # from syscolumns a,syscolumns bselect 共有天数=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
) adrop table #/*
共有天数
-----------
735(所影响的行数为 1 行)
*/
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*/
即实现@yyear1到yyear2年间2月份总共多少天?能实现吗?感谢!
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
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
goexec my_proc 1980 , 2005
drop procedure my_proc/*
result = 735
*/
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
goexec my_proc 1980 , 2005
drop procedure my_proc/*
result = 735
*/