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

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

tired_bird 2007-12-16 07:53:48
请问如何用时间函数实现某两年间2月份总共有多少天?

例如实现1980年到2005年间2月份总共多少天?
...全文
197 点赞 收藏 19
写回复
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
dobear_0922 2007-12-23
全部按28天算,再加上中间闰年次数,,,
回复
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
*/
回复
中国风 2007-12-23
没遇到楼主说的情况,这样试试..


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

回复
tired_bird 2007-12-23
在运行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)
语句时,却处于一直查询状态,不出结果,为什么,我可能哪步出错了
回复
JL99000 2007-12-23
方法多多
不再写了
主要是个遍历查询的过程
回复
liangCK 2007-12-16
BS技术帖接分..
回复
-狙击手- 2007-12-16
接分
回复
liangCK 2007-12-16
那给分结帖吧.
回复
tired_bird 2007-12-16
谢谢,刚才没看到roy_88的函数!
回复
liangCK 2007-12-16
那你使用roy_88的函数就可以了啊.
回复
tired_bird 2007-12-16
先谢谢大家的帮忙!

但如果把1980 和 2005 换成变量@yyear1 和 @yyear2 呢?
即实现@yyear1到yyear2年间2月份总共多少天?能实现吗?感谢! 
回复
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
(
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 行)
*/
回复
中国风 2007-12-16

--只计算二月份的天数和
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
(
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 行)
*/
回复
中国风 2007-12-16
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 2007-12-16

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 #
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告