高分 求一个计算日期的函数

[eyis]

参数：@Year varchar(4)，@Month varchar(2)
输出结果
————————————————————
2005 5 1
2005 5 2
2005 5 3
...
2005 5 31

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

3Q

[paoluo]

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 行）

[playwarcraft]

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

[paoluo]

--創建函數
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
*/

[ojuju10]

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')

[ojuju10]

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

[wgzaaa]

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

[hyc_music1981]

好多人啊

[darkloud]

怎么都这么快啊

[lijingengg]

15G全能空间年付500元/月付70元 可免费试用
全国第一家虚拟主机:支持伪静态.有利于提高排名
5GB 独立WEB空间、5GB 企业邮箱空间、5GB MSSQL数据库
IIS连接数据 500 个、500GB/月流量、共享日志文件空间

联系QQ:1012727
详情咨询021-51695858
官方网站: www.abcnic.com

企业邮箱功能
赠送5GB 超大企业邮箱，500个Email企业邮箱用户
自动回复、自动转发、POP3、SMTP收发信、SMTP发信认证
邮件过滤、邮件拒收、邮件夹管理、邮件域管理、定制邮件数
数据库功能
支持5GB MSSQL数据库空间，5个用户数据库、Access
主机功能支持
采用安全稳定的Win2003 .net2.0 架构
支持ASP、PHP、ASP.NET、PERL等脚本、支持自定义CGI
全面支持.net2.0版本，独立的Application应用池，
支持SSI（Shtml），支持FrontPage扩展
可免费自行绑定5个域名、500个解析、500个子域名

动易内容管理系统,数字人信息港,乔客CMS系统,SuperSite
动网论坛系列,
Discuz系列,PhpWind社区,BBSXP论坛
ShopEx商城,时代网店系统 ,多用户拍卖系统,
逍遥商城
SugarCRM,vtiger,CRM,OBM,WebERP
Mambo智能建站,XOOPS建站系统,
Joomla智能建站,Dede织梦系
关键词：虚拟主机 空间租用 免费空间 ASP空间 PHP空间 NET空间 ASP虚拟主机
PHP虚拟主机 NET虚拟 BLOG虚拟主机 全能虚拟主机 全能空间 数据库 程序虚拟主机

[梅小西Echo]

学习了

[PCI_E]

create function f_test(@year varchar(4),@mon varchar(2))
returns @t table(d varchar(10))
as
begin
declare @beginDate varchar(10),@endDate varchar(10)
declare @dayNum int
declare @i int
select @beginDate = @year+'-'+@month+'-01'
select @endDate = dateadd(mm,1,@beginDate)
select @dayNum = datediff(dd,@beginDate,@endDate)

while @i <=@dayNum
begin
insert @t
select @beginDate
select @beginDate = dateadd(dd,1,@beginDate)
set @i=@i+1
end

return
end
go

[best8625]

alter Function F_GetDate(@Year varchar(4), @Month varchar(2))
Returns @Date Table([year] int,[month] int,[day] int)
As
Begin
Declare @BeginDate DateTime, @EndDate DateTime
set @BeginDate = @Year + '-' + @Month + '-01'
set @EndDate = DateAdd(mm, 1, @Year + '-' + @Month + '-01')
Select @EndDate = Convert(Varchar(7), @EndDate, 120) + '-01'
While @BeginDate < @EndDate
Begin
Insert into @Date
Select datepart("yy",@BeginDate),datepart("month",@BeginDate),datepart("dd",@BeginDate)
Select @BeginDate = DateAdd(dd, 1, @BeginDate)
End
Return
End
GO


select * from F_GetDate('2008','12')

year month day
----------- ----------- -----------
2008 12 1
2008 12 2
2008 12 3
2008 12 4
2008 12 5
2008 12 6
2008 12 7
2008 12 8
2008 12 9
2008 12 10
2008 12 11
2008 12 12
2008 12 13
2008 12 14
2008 12 15
2008 12 16
2008 12 17
2008 12 18
2008 12 19
2008 12 20
2008 12 21
2008 12 22
2008 12 23
2008 12 24
2008 12 25
2008 12 26
2008 12 27
2008 12 28
2008 12 29
2008 12 30
2008 12 31

(31 行受影响)

[nettman]

Mark!

[fa_ge]

答案好多，簡潔的也有啊

[sp4]

太多了

[ken2002]

方法真多呀，学习了！

[herosea_xuj]

mark

[kofkyo]

都好快
我就看吧

[laowang2]

有时候拿来主义就是好。