生成指定日期段的日期列表,月份列表

发布时间：2020-12-12 14:11:35 所属栏目：MsSql教程来源：网络整理

导读：if exists(select * from sys.sysobjects where id = OBJECT_ID(N'[dbo].[f_getdate]') and xtype in (N'FN',N'IF',N'TF')) drop function [dbo].[f_getdate] go /*--生成列表生成指定日期段的日期列表 --查询工作日 SELECT * FROM dbo.f_getdate('2005-1-3

if exists(select * from sys.sysobjects where id = OBJECT_ID(N'[dbo].[f_getdate]') and xtype in (N'FN',N'IF',N'TF'))
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 left join ( select [month]= MONTH(Date),[money]= SUM([money]) from @t group by MONTH(Date)? ) b on a.[month] = b.[month]

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!