drop function [dbo].[f_getdate]
go
/*--生成列表
生成指定日期段的日期列表
--查询工作日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
--查询休息日
SELECT * FROM dbo.f_getdate('2005-1-3',1)
--查询全部日期
SELECT * FROM dbo.f_getdate('2005-1-3',NulL)
--*/
create function dbo.f_getdate(
@begin_date datetime,
@end_date datetime,
@bz bit
)returns @re table(ID int IDentity(1,1),Date datetime,Weekday nvarchar(13))
as
begin
declare @tb table(ID int IDentity(0,a bit)
insert into @tb select top 366 0 from sysobjects a,sysobjects b
if @bz = 0
while @begin_date <= @end_date
begin
insert into @re(Date,Weekday)
select Date,DATEname(Weekday,Date)
from (
select Date = DATEADD(day,ID,@begin_date)
from @tb
) a where Date <= @end_date and
(DATEPART(Weekday,Date) + @@DATEFirsT - 1) % 7 between 1 and 5
set @begin_date = DATEADD(day,366,@begin_date)
end
else if @bz = 1
while @begin_date <=@end_date
begin
insert into @re(Date,Date) from
(
select Date = DATEADD(day,@begin_date)
from @tb
) a where Date <= @end_date
and (DATEPART(Weekday,Date) + @@DATEFirsT - 1) % 7 in (0,6)
set @begin_date = DATEADD(day,@begin_date)
end
else
while @begin_date < @end_date
begin
insert into @re(Date,Date) from
(
select Date = DATEadd(day,@begin_date)
from @tb
) a where Date <= @end_date
set @begin_date = DATEADD(day,@begin_date)
end
return
end
go
SELECT * FROM dbo.f_getdate('2012-12-31','2013-12-31',0)
=========================
use sqltest go declare @t table(ID int,date datetime,[money] decimal(10,2)) insert into @t select 1,'2005-1-1',10 union all select 2,'2005-2-21',20 union all select 3,'2005-3-1',30 union all select 2,40 union all select 3,'2005-5-1',50 union all select 2,'2005-11-21',60 union all select 3,'2005-11-11',70 select a.[MONTH],[money] = ISNulL(b.[money],0) from ( select [MONTH] = 1 union all select [MONTH] = 2 union all select [MONTH] = 3 union all select [MONTH] = 4 union all select [MONTH] = 5 union all select [MONTH] = 6 union all select [MONTH] = 7 union all select [MONTH] = 8 union all select [MONTH] = 9 union all select [MONTH] = 10 union all select [MONTH] = 11 union all select [MONTH] = 12 )a @R_419_6823@ join ( select [month]= MONTH(Date),[money]= SUM([money]) from @t group by MONTH(Date) ) b on a.[month] = b.[month]
@H_301_98@ 总结以上是内存溢出为你收集整理的生成指定日期段的日期列表,月份列表全部内容,希望文章能够帮你解决生成指定日期段的日期列表,月份列表所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)