• 主页
• 基础类
• 应用实例
• 新技术前沿

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

tired_bird 2007-12-16 07:53:48

...全文
197 点赞 收藏 19

19 条回复

dobear_0922 2007-12-23

dawugui 2007-12-23
``````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 2007-12-23
``````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
*/
``````

``````

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),
) ,
@startDate=@startDate+1
return   @MonthDay
end

go
select   dbo.F_year(2006,2007)

--drop function F_year
``````

tired_bird 2007-12-23

create function F_year(@startDate int,@EndDate int)
returns int
as
begin
declare @MonthDay int
set @MonthDay=0
while @startDate!>@EndDate
@startDate=@startDate+1
return @MonthDay
end

JL99000 2007-12-23

liangCK 2007-12-16
BS技术帖接分..

-狙击手- 2007-12-16

liangCK 2007-12-16

tired_bird 2007-12-16

liangCK 2007-12-16

tired_bird 2007-12-16

liangCK 2007-12-16

xinleile 2007-12-16
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 2007-12-16

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

select 共有天数=sum(days) from
(
from
(
from #
) b
) a

drop table #

/*

-----------
735

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

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

-----------
791

（所影响的行数为 1 行）
``````

liangCK 2007-12-16
``````select top 1000 id=identity(int,1,1) into # from syscolumns a,syscolumns b

select 共有天数=sum(days) from
(
from
(
from #
) b
) a

drop table #

/*

-----------
706

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

``````create function F_year(@startDate int,@EndDate int)
returns int
as
begin
declare @MonthDay int
set @MonthDay=0
while @startDate!>@EndDate
@startDate=@startDate+1
return @MonthDay
end
--
select dbo.F_year(2006,2007)``````

liangCK 2007-12-16
``````
select top 1000 id=identity(int,1,1) into # from syscolumns a,syscolumns b

select 共有天数=sum(days) from
(
from
(
from #
) b
) a

drop table #``````

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区