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

最新推荐文章于 2022-10-15 20:15:47 发布

ijwsoft

最新推荐文章于 2022-10-15 20:15:47 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/keenweiwei/article/details/50737498

本文介绍了一个SQL Server存储过程，用于动态计算指定月份每周的起始与结束日期，并特别处理了二月的特殊情况。该过程能够适应不同月份天数的变化，为报表生成提供便利。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景：有时侯导报表需要动态计算每个月对应每周的第一天与最后一天。最后一周的时间要求不需要仅精确到第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

运行结果：