SQL 输出某一天所在的年月对应的所有星期的开始与结束日期（每个月仅算4周，第四周可能会有10天）

背景：有时侯导报表需要动态计算每个月对应每周的第一天与最后一天。最后一周的时间要求不需要仅精确到第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 ONGOSET QUOTED_IDENTIFIER ONGO/*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)ASBEGIN       DECLARE @month VARCHAR(2)DECLARE @year VARCHAR(4)DECLARE @FirstDayInFeb DATETIMEDECLARE @LastDayInFeb DATETIMEif(@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=2INSERT INTO @tWeek VALUES  (  @year+'-'+@month+'-22',  CONVERT(VARCHAR(10),@LastDayInFeb,120),4)SELECT * FROM @tWeek  END GO

运行结果：