背景:有时侯导报表需要动态计算每个月对应每周的第一天与最后一天。最后一周的时间要求不需要仅精确到第7天,可以算10天。
USE [***]
GO
/****** Object: StoredProcedure [dbo].[sp_CalcStartDateEndDateForSingleYearMonthInDate] Script Date: 2016/2/25 14:28:26 ******/
DROP PROCEDURE [dbo].[sp_CalcStartDateEndDateForSingleYearMonthInDate]
GO
/****** Object: StoredProcedure [dbo].[sp_CalcStartDateEndDateForSingleYearMonthInDate] Script Date: 2016/2/25 14:28:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec [sp_CalcStartDateEndDateForSingleYearMonthInDate] '2016-1-1'
exec [sp_CalcStartDateEndDateForSingleYearMonthInDate] '2015-10-1'
exec [sp_CalcStartDateEndDateForSingleYearMonthInDate] '2015-9-1'
exec [sp_CalcStartDateEndDateForSingleYearMonthInDate] '2014-3-1'
*/
CREATE PROC [dbo].[sp_CalcStartDateEndDateForSingleYearMonthInDate](
@SingleDate datetime
)
AS
BEGIN
DECLARE @month VARCHAR(2)
DECLARE @year VARCHAR(4)
DECLARE @FirstDayInFeb DATETIME
DECLARE @LastDayInFeb DATETIME
if(@SingleDate is null)
set @SingleDate=getdate()
SET @month= MONTH(@SingleDate)
SET @year=YEAR(@SingleDate)
SET @FirstDayInFeb = CONVERT(DATETIME, @year+'-02-01')
SET @LastDayInFeb = DATEADD(M, 1, @FirstDayInFeb)
SET @LastDayInFeb = DATEADD(D, -1, @LastDayInFeb)
--drop table @tWeek
--CREATE TABLE @tWeek
--(
--startDate VARCHAR(10),
--endDate VARCHAR(10),
--weekIndex INT
--)
declare @tWeek TABLE
(
startDate VARCHAR(10),
endDate VARCHAR(10),
weekIndex INT
)
INSERT INTO @tWeek VALUES ( @year+'-'+@month+'-1',@year+'-'+@month+'-7', 1)
INSERT INTO @tWeek VALUES ( @year+'-'+@month+'-8',@year+'-'+@month+'-14', 2)
INSERT INTO @tWeek VALUES ( @year+'-'+@month+'-15',@year+'-'+@month+'-21', 3)
IF @month IN (1,3,5,7,8,10,12)
INSERT INTO @tWeek VALUES ( @year+'-'+@month+'-22',@year+'-'+@month+'-31', 4)
IF @month IN (4,6,9,11)
INSERT INTO @tWeek VALUES ( @year+'-'+@month+'-22',@year+'-'+@month+'-30', 4)
IF @month=2
INSERT INTO @tWeek VALUES ( @year+'-'+@month+'-22', CONVERT(VARCHAR(10),@LastDayInFeb,120),4)
SELECT * FROM @tWeek
END
GO
运行结果: