当前位置：首页 > news >正文

SQL server 2008 获取当前年，季度和月的最后一天

news 来源：原创 2024/9/19 14:01:15

IF @DateThreshold IS NULL
SET @DateThreshold = CONVERT(DATE, GETDATE() - 1)

   DECLARE @Date DATE = @DateThreshold

   -- 获取年份和季度
   DECLARE @Year INT = YEAR(@Date)
   DECLARE @Quarter INT = (MONTH(@Date) - 1) / 3 + 1

   -- 计算下季度的最后一个月份的第一天
   DECLARE @FirstDayOfNextQuarter DATE = DATEADD(MONTH, @Quarter * 3, DATEADD(YEAR, @Year - 1900, 0))
   DECLARE @LastDayOfQuarter DATE = DATEADD(DAY, -1, @FirstDayOfNextQuarter)
    -- 减去一天得到当前季度的最后一天
   print(@LastDayOfQuarter)

   DECLARE @LastDayOfYear DATE = CAST(CAST(@Year AS VARCHAR) + '-12-31' AS DATE);
   print(@LastDayOfYear)

   -- 直接计算该月份的最后一天
   DECLARE @LastDayOfMonth DATE = DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOfMonth)

   -- 直接计算上1个月份的最后一天
   DECLARE @LastDayOf1Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf1Month)

   -- 直接计算上2个月份的最后一天
   DECLARE @LastDayOf2Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf2Month)

   -- 直接计算上3个月份的最后一天
   DECLARE @LastDayOf3Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf3Month)

   -- 直接计算上4个月份的最后一天
   DECLARE @LastDayOf4Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf4Month)

   -- 直接计算上5个月份的最后一天
   DECLARE @LastDayOf5Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -4, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf5Month)

   -- 直接计算上6个月份的最后一天
   DECLARE @LastDayOf6Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -5, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf6Month)

   -- 直接计算上7个月份的最后一天
   DECLARE @LastDayOf7Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -6, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf7Month)

   -- 直接计算上8个月份的最后一天
   DECLARE @LastDayOf8Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -7, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf8Month)

   -- 直接计算上9个月份的最后一天
   DECLARE @LastDayOf9Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -8, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf9Month)

   -- 直接计算上10个月份的最后一天
   DECLARE @LastDayOf10Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -9, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf10Month)

   -- 直接计算上11个月份的最后一天
   DECLARE @LastDayOf11Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -10, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf11Month)

   -- 直接计算上12个月份的最后一天
   DECLARE @LastDayOf12Month DATE = DATEADD(DAY, -1, DATEADD(MONTH, -11, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)))
   print(@LastDayOf12Month)