SQL SERVER 实现当月日历

最新推荐文章于 2024-08-19 03:44:07 发布

smahorse

最新推荐文章于 2024-08-19 03:44:07 发布

阅读量597

点赞数

文章标签： sql server date calendar 日历 sun 存储

入参日期

输出当月日历表

具体存储过程如下：

USE TEST
GO

ALTER PROCEDURE Study_TEST_Calendar
(
	@Date DATETIME
)
	AS
	BEGIN
	SET NOCOUNT ON 

	SELECT SUN=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))=1 THEN  DATEPART(DD,DATEADD(DD,1-DAY(@Date)+number,@Date)) ELSE '' END),
			MON=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))=2 THEN DATEPART(DD,DATEADD(DD,1-DAY(@Date)+number,@Date)) ELSE '' END),
			TUE=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))=3 THEN DATEPART(DD,DATEADD(DD,1-DAY(@Date)+number,@Date)) ELSE '' END),
			WED=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))=4 THEN DATEPART(DD,DATEADD(DD,1-DAY(@Date)+number,@Date)) ELSE '' END),
			THU=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))=5 THEN DATEPART(DD,DATEADD(DD,1-DAY(@Date)+number,@Date)) ELSE '' END),
			FRI=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))=6 THEN DATEPART(DD,DATEADD(DD,1-DAY(@Date)+number,@Date)) ELSE '' END),
			SAT=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))=7 THEN DATEPART(DD,DATEADD(DD,1-DAY(@Date)+number,@Date)) ELSE '' END)
	FROM master..spt_values 
	WHERE type='P'
	AND DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date))<DATEADD(MM,1,DATEADD(DD,1-DAY(@Date),@Date))
	GROUP BY DATEPART(WEEK,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))

	END

部分日期元素拆解:

	DECLARE @Date DATETIME
    SET @Date=GETDATE()
	SELECT number
			,DATEPART(WEEK,DATEADD(DD,number,DATEADD(DD,1-DAY(@Date),@Date)))
			,DATEPART(weekday,DATEADD(DAY,number,DATEADD(DD,1-DAY(@Date),@Date))) 
		   ,DATEADD(DAY,number,DATEADD(DD,1-DAY(@Date),@Date))
		   ,LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))))
		   ,LTRIM(DAY(DATEADD(DAY,1-DAY(@DATE)+number,@DATE)))
		   ,DATEPART(DD,(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))))
		   ,DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))
		   ,DATEPART(DD,DATEADD(DD,1-DAY(@Date)+number,@Date))
	FROM master..spt_values 
	WHERE type='P'
	AND DATEADD(DAY,number,DATEADD(DD,1-DAY(@Date),@Date))<DATEADD(MM,1,DATEADD(DD,1-DAY(@Date),@Date))