USE [CYBPM2DBIPCMS_Dev]
GO
/****** Object: UserDefinedFunction [dbo].[F0GetYearMonthList] Script Date: 07/12/2018 11:51:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[F0GetYearMonthList]
(
@BeginDate DATETIME,
@EndDate DATETIME,
@Type INT =0--查询方式 0:年度 1:季度 2:月 3:周
)
RETURNS @YearMonth TABLE
(
TimeStr NVARCHAR(20),--时间组合字符串
[Year] INT,--年
[Quarter] INT,--季
[Week] INT,--当年第几周
[MonthWeek] INT,--当月第几周
[Month] INT --月
,[Stime] DATETIME --当前开始日期
,[Etime] DATETIME --当前结束日期
)
/*
功能:根据开始日期 结束日期,返回时间列表
参数:
返回:返回时间列表表
编写: LJR 2018-7-12
*/
AS
BEGIN
IF @Type =0--年
BEGIN
WHILE( YEAR(@BeginDate) <= YEAR(@EndDate) )
BEGIN
INSERT INTO @YearMonth(TimeStr, [Year],[Stime],[Etime])
SELECT CAST(YEAR(@BeginDate) AS VARCHAR)+ '年', YEAR(@BeginDate)
,DATEADD(YEAR, DATEDIFF(YEAR,0,@BeginDate), 0),DATEADD(MS,-3,DATEADD(YEAR, DATEDIFF(YEAR,0,@BeginDate)+1, 0))
SELECT @BeginDate = DATEADD(YEAR, 1, @BeginDate)
END
END
ELSE IF @Type = 1--季度
BEGIN
WHILE( @BeginDate <= @EndDate )
BEGIN
INSERT INTO @YearMonth(TimeStr, [Year],[Quarter],[Stime],[Etime])
SELECT CAST(YEAR(@BeginDate) AS VARCHAR)+ '年第' + RIGHT(CAST(DATENAME(QUARTER,@BeginDate) AS VARCHAR), 1)+'季度', YEAR(@BeginDate)
,DATENAME(QUARTER,@BeginDate),DATEADD(QUARTER, DATEDIFF(QUARTER,0,@BeginDate), 0)
,DATEADD(MS,-3,DATEADD(QUARTER, DATEDIFF(QUARTER,0,@BeginDate)+1, 0))
SELECT @BeginDate = DATEADD(QUARTER, 1, @BeginDate)
END
END
ELSE IF @Type = 2--月
BEGIN
WHILE( @BeginDate <= @EndDate )
BEGIN
INSERT INTO @YearMonth(TimeStr, [Year], [Month],[Quarter],[Stime],[Etime])
SELECT CAST(YEAR(@BeginDate) AS VARCHAR)+ '年' + CAST(MONTH(@BeginDate) AS VARCHAR)+'月', YEAR(@BeginDate)
, MONTH(@BeginDate),DATENAME(QUARTER,@BeginDate),DATEADD(DAY,-Day(@BeginDate)+1,@BeginDate)
,DATEADD(MS,-3,DATEADD(MONTH, DATEDIFF(MONTH,0,@BeginDate)+1, 0))
SELECT @BeginDate = DATEADD(MONTH, 1, @BeginDate)
END
END
ELSE IF @Type = 3--周
BEGIN
WHILE( DATEADD(day,-(DATEPART(weekday,@BeginDate)-1),@BeginDate) <= @EndDate )
BEGIN
--解读:DATEPART(WEEK,时间),是取当前时间是本年第几周,dateadd(dd,-day(时间),时间)当前时间前一个月最后一天
--计算本月第几周=当前时间本年第几周-当前时间前一个月最后一天在本年第几周
--注意::::::::::翻年的情况,比如2018-01月,那么前一个月就是2017-12-31,这种就不需要减去前一个月的周
--注意::::::::::每年的第一个月,也是不需要减去前一个月的周,在当前年是第几周在月就是第几周
DECLARE @YearWeek INT --本年第几周
DECLARE @MonthWeek INT --本月第几周
SET @YearWeek=DATEPART(WEEK,@BeginDate)
SELECT @MonthWeek = CASE MONTH(@BeginDate) WHEN 1 THEN @YearWeek ELSE @YearWeek-DATEPART(WEEK,dateadd(dd,-day(@BeginDate),@BeginDate))+1 END
--本周的最后一天
DECLARE @nextWeekStime datetime
SET @nextWeekStime=DATEADD(day,-(DATEPART(weekday,@BeginDate)-7),@BeginDate) --本周的最后一天
--插入数据
INSERT INTO @YearMonth(TimeStr, [Year], [Month],[Quarter],[Week],[MonthWeek],[Stime],[Etime])
SELECT CAST(YEAR(@BeginDate) AS VARCHAR)+ '年' + CAST(MONTH(@BeginDate) AS VARCHAR)+'月第'+CAST( @MonthWeek AS NVARCHAR(2))+'周'
, YEAR(@BeginDate), MONTH(@BeginDate),DATENAME(QUARTER,@BeginDate),@YearWeek ,@MonthWeek
,DATEADD(day,-(DATEPART(weekday,@BeginDate)-1),@BeginDate),DATEADD(MS,-3,@nextWeekStime)
--给下一周初始日期赋值
--注意::::::::::如果这里刚好一个星期翻年了,就会造成下一年少了第一个周,所以我们需要特殊处理一下
--注意::::::::::如果这里刚好一个星期翻月了,就会造成下一月少了第一个周,所以我们需要特殊处理一下
--记录初始年份,月份
DECLARE @Year INT
SET @Year =YEAR(@BeginDate)
DECLARE @Month INT
SET @Month=MONTH(@BeginDate)
SELECT @BeginDate = DATEADD(DAY, 1, @nextWeekStime)--本周最后一天在加一天,就是下周第一天
--判断更新后的年份是否翻年
IF @Year < YEAR(@BeginDate)
BEGIN
SET @BeginDate=DATEADD(YEAR, DATEDIFF(YEAR,0,@BeginDate), 0)
END
--判断更新后的月份是否翻月
IF @Month < MONTH(@BeginDate)
BEGIN
SET @BeginDate=DATEADD(dd,-Day(@BeginDate)+1,@BeginDate)
END
END
END
RETURN
END
示例:
select * from [dbo].[F0GetYearMonthList]('2017-01-01', '2018-10-1',0)
select * from [dbo].[F0GetYearMonthList]('2017-01-01', '2018-10-16',1)
select * from [dbo].[F0GetYearMonthList]('2017-01-01', '2018-10-16',2)
select * from [dbo].[F0GetYearMonthList]('2017-01-01', '2018-10-16',3)
————————————————
版权声明:本文为CSDN博主「容妞」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/lijingrong_ljr/article/details/81010897