SQL Server一个打印日历的函数

最新推荐文章于 2023-04-07 10:37:21 发布

武哥博文

最新推荐文章于 2023-04-07 10:37:21 发布

阅读量958

点赞数

CC 4.0 BY-SA版权

分类专栏： 001-A 数据库开发文章标签： sql server 日历 calendar sun insert function

本文链接：https://blog.youkuaiyun.com/GRANDTREE/article/details/5729710

001-A 数据库开发专栏收录该内容

146 篇文章

订阅专栏

本文介绍了一个SQL函数，用于生成指定年的月历表格，包括月份、周数及每周各天的最大日期。通过该函数，可以方便地查看整年的日历布局。

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

CREATE FUNCTION PrintCalendar(@Year CHAR(4))
RETURNS
@Calendar TABLE
(
  [Month] VARCHAR(20),
  WeekNum CHAR(2),
  Sun INT,
  Mon INT,
  Tues INT,
  Wed INT,
  Thur INT,
  Fri INT,
  Sat INT
)
AS
BEGIN
DECLARE @FirstDayOfYear DATETIME
SET @FirstDayOfYear = CONVERT(DATETIME, @Year+'-01-01')

INSERT INTO @Calendar([Month],WeekNum, Sun, Mon, Tues, Wed, Thur, Fri, Sat )
SELECT
  DATENAME(MONTH,DATEADD(DAY,number,@FirstDayOfYear)),
  DATEPART(week,dateadd(DAY,number,@FirstDayOfYear)) week_num,
  MAX(CASE WHEN DATEPART(DW,DATEADD(DAY,number,@FirstDayOfYear)) = 1
     THEN DATEPART(DAY,DATEADD(DAY,number,@FirstDayOfYear))
     END
   ) AS Sunday,
  MAX(CASE WHEN DATEPART(DW,DATEADD(DAY,number,@FirstDayOfYear)) = 2
     THEN DATEPART(DAY,DATEADD(DAY,number,@FirstDayOfYear))
   END) AS Monday,
  MAX(CASE WHEN DATEPART(DW,DATEADD(DAY,number,@FirstDayOfYear)) = 3
     THEN DATEPART(DAY,DATEADD(DAY,number,@FirstDayOfYear))
            END) AS Tuesday,
  MAX(CASE WHEN DATEPART(DW,DATEADD(DAY,number,@FirstDayOfYear)) = 4
     THEN DATEPART(DAY,DATEADD(DAY,NUMBer,@FirstDayOfYear))
   END) AS Wednesday,
  MAX(CASE WHEN DATEPART(DW,DATEADD(DAY,number,@FirstDayOfYear)) = 5
     THEN DATEPART(DAY,DATEADD(DAY,number,@FirstDayOfYear))
   END) AS Thursday,
  MAX(CASE WHEN DATEPART(DW,DATEADD(DAY,number,@FirstDayOfYear)) = 6
     THEN DATEPART(DAY,DATEADD(DAY,number,@FirstDayOfYear))
   END) AS Friday,
  MAX(CASE WHEN DATEPART(DW,DATEADD(DAY,number,@FirstDayOfYear)) = 7
     THEN DATEPART(DAY,DATEADD(DAY,number,@FirstDayOfYear))
   END) AS Saturday
FROM master..spt_values
WHERE TYPE ='P'
  AND DATEPART(YEAR,DATEADD(DAY,number,@FirstDayOfYear)) = @Year
GROUP BY DATENAME(MONTH,DATEADD(DAY, number, @FirstDayOfYear)),
    DATEPART(MONTH,DATEADD(DAY, number, @FirstDayOfYear)),
    DATEPART(WEEK,DATEADD(DAY, number, @FirstDayOfYear))
ORDER BY DATEPART(MONTH,DATEADD(DAY, number, @FirstDayOfYear)),
    DATEPART(WEEK, DATEADD(DAY, number, @FirstDayOfYear))

RETURN
END