高分 求一个计算日期的函数
参数：@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.000
2007-07-30 00:00:00.000
2007-07-31 00:00:00.000
（所影响的行数为 31 行）
[解决办法]
create function fn_test(@year varchar(04),@month varchar(02))
returns @t table([date] datetime)
AS
begin
declare @begin datetime,@end datetime
select @begin=convert(datetime,@year+ '- '+@month+ '-01 ')
select @end=dateadd(day,-1,dateadd(month,1,@begin))
insert into @t select @begin
while @begin <@end
begin
insert into @t
select dateadd(day,1,@begin)
set @begin=dateadd(day,1,@begin)
end
return
end
[解决办法]
--創建函數
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 ')
--結果
/*
MonthDate
2007-05-01
2007-05-02
2007-05-03
2007-05-04
2007-05-05
2007-05-06
2007-05-07
2007-05-08
2007-05-09
2007-05-10
2007-05-11
2007-05-12
2007-05-13
2007-05-14
2007-05-15
2007-05-16
2007-05-17
2007-05-18
2007-05-19
2007-05-20
2007-05-21
2007-05-22
2007-05-23
2007-05-24
2007-05-25
2007-05-26
2007-05-27
2007-05-28
2007-05-29
2007-05-30
2007-05-31
*/
[解决办法]
create function f_test(@year varchar(4),@mon varchar(2))
returns @t table(createtime datetime)
as
begin
declare @i int
set @i=1
while @i <=31 and isdate(@year+ '- '+@mon+ '- '+ltrim(@i))=1
begin
insert into @t select cast(@year+ '- '+@mon+ '- '+ltrim(@i) as datetime)
set @i=@i+1
end
return
end
select * from f_test( '2007 ', '02 ')
[解决办法]
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
[解决办法]
CREATE FUNCTION FUN_DAYS(@Year varchar(4),@Month varchar(2)) RETURNS TABLE AS
return select top 100 percent @year+ '- '+@Month+ '- '+rtrim(langid+1) 日期
from master.dbo.syslanguages
where langid+1 <=day(dateadd(dd,-1,dateadd(mm,1,@Year+ '- '+@Month+ '-1 ')))
order by langid
-----------------------------------
select * from FUN_DAYS( '2007 ', '2 ')
------------------------
2007-2-1
2007-2-2
2007-2-3
2007-2-4
2007-2-5
2007-2-6
2007-2-7
2007-2-8
2007-2-9
2007-2-10
2007-2-11
2007-2-12
2007-2-13
2007-2-14
2007-2-15
2007-2-16
2007-2-17
2007-2-18
2007-2-19
2007-2-20
2007-2-21
2007-2-22
2007-2-23
2007-2-24
2007-2-25
2007-2-26
2007-2-27
2007-2-28
[解决办法]
declare @year varchar(4),@month varchar(2)
select @year= '2008 ',@month= '2 '
declare @startdate datetime
select @startdate=@year+ '- '+right(100+@month,2)+ '- '+ '01 '
select @startdate+num from
(
select top 31 id,num=(select count(0) from sys.sysobjects where id <a.id) from sys.sysobjects a
) t
where year(@startdate+num)=@year and month(@startdate+num)=@month
[解决办法]
create proc sp_getdate( @year varchar(4) , @month varchar(2) )
as
begin
declare @date_cur datetime
declare @date_nxt datetime
create table #t( dt datetime)
if len(rtrim(ltrim(@month))) <> '2 ' select @month = '0 ' + @month
select @date_cur = cast( @year + '- ' + @month + '- ' + '01 ' as datetime)
select @date_nxt = dateadd(month , 1 , @date_cur)
while @date_cur < @date_nxt
begin
insert into #t
select @date_cur
select @date_cur = dateadd(day , 1 , @date_cur)
end
select * from #t
drop table #t
end
exec sp_getdate '2007 ' , '07 '
