日期:2014-05-19  浏览次数:20403 次

高分 求一个计算日期的函数
参数:@Year   varchar(4),@Month   varchar(2)
输出结果
————————————————————
2005   5   1
2005   5   2
2005   5   3
...
2005   5   31

即输入某年某月   出来那一月的所有天数结果集

3Q


------解决方案--------------------
create function fn_Days(
@Year varchar(4),
@Month varchar(2)
)
returns @r table(
D datetime
)
as
begin
declare @i int
set @i=1
while @i <=31 and isdate(@Year+ '- '+@Month+ '- '+cast(@i as varchar))=1
begin
insert @r values(@Year+ '- '+@Month+ '- '+cast(@i as varchar))
set @i=@i+1
end
return
end
go

--测试
select * from dbo.fn_Days( '2007 ', '2 ')

--结果
D
------------------------------------------------------
2007-02-01 00:00:00.000
2007-02-02 00:00:00.000
2007-02-03 00:00:00.000
2007-02-04 00:00:00.000
2007-02-05 00:00:00.000
2007-02-06 00:00:00.000
2007-02-07 00:00:00.000
2007-02-08 00:00:00.000
2007-02-09 00:00:00.000
2007-02-10 00:00:00.000
2007-02-11 00:00:00.000
2007-02-12 00:00:00.000
2007-02-13 00:00:00.000
2007-02-14 00:00:00.000
2007-02-15 00:00:00.000
2007-02-16 00:00:00.000
2007-02-17 00:00:00.000
2007-02-18 00:00:00.000
2007-02-19 00:00:00.000
2007-02-20 00:00:00.000
2007-02-21 00:00:00.000
2007-02-22 00:00:00.000
2007-02-23 00:00:00.000
2007-02-24 00:00:00.000
2007-02-25 00:00:00.000
2007-02-26 00:00:00.000
2007-02-27 00:00:00.000
2007-02-28 00:00:00.000

(所影响的行数为 28 行)


------解决方案--------------------
Create Function F_GetDate(@Year varchar(4), @Month varchar(2))
Returns @Date Table(MonthDate Varchar(10))
As
Begin
Declare @BeginDate DateTime, @EndDate DateTime
Select @BeginDate = @Year + '- ' + @Month + '-01 ', @EndDate = DateAdd(mm, 1, @Year + '- ' + @Month + '-01 ')
Select @EndDate = Convert(Varchar(7), @EndDate, 120) + '-01 '
While @BeginDate < @EndDate
Begin
Insert @Date Select Convert(Varchar(10), @BeginDate, 120)
Select @BeginDate = DateAdd(dd, 1, @BeginDate)
End
Return
End
GO
Select * From dbo.F_GetDate( '2007 ', '05 ')
------解决方案--------------------
--测试
select * from dbo.fn_Days( '2007 ', '7 ')

--结果
D
------------------------------------------------------
2007-07-01 00:00:00.000
2007-07-02 00:00:00.000
2007-07-03 00:00:00.000
2007-07-04 00:00:00.000
2007-07-05 00:00:00.000
2007-07-06 00:00:00.000
2007-07-07 00:00:00.000
2007-07-08 00:00:00.000
2007-07-09 00:00:00.000
2007-07-10 00:00:00.000
2007-07-11 00:00:00.000
2007-07-12 00:00:00.000
2007-07-13 00:00:00.000
2007-07-14 00:00:00.000
2007-07-15 00:00:00.000
2007-07-16 00:00:00.000
2007-07-17 00:00:00.000
2007-07-18 00:00:00.000
2007-07-19 00:00:00.000
2007-07-20 00:00:00.000
2007-07-21 00:00:00.000
2007-07-22 00:00:00.000
2007-07-23 00:00:00.000
2007-07-24 00:00:00.000
2007-07-25 00:00:00.000
2007-07-26 00:00:00.000
2007-07-27 00:00:00.000
2007-07-28 00:00:00.000
2007-07-29 00:00:00