SQL取上个月所有的工作日对应的天数

摘要

-- 取上个月所有的工作日对应的天数
DECLARE @t TABLE(dt DATETIME)
DECLARE @dtStart DATETIME
DECLARE @dtEnd DATETIME

SET @dtStart=DATEADD(mm,DATEDIFF(mm,0,dateadd(month,-1,getdate())),0)--上月第一 
SET @dtEnd=datea...

-- 取上个月所有的工作日对应的天数
DECLARE @t TABLE(dt DATETIME)
DECLARE @dtStart DATETIME
DECLARE @dtEnd DATETIME

SET @dtStart=DATEADD(mm,DATEDIFF(mm,0,dateadd(month,-1,getdate())),0)--上月第一 
SET @dtEnd=dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))--上月最后一

DECLARE @WeekDay int=7

DECLARE @WeekDayCount TABLE ([WeekDay] INT, WeekDayEnglish VARCHAR(10), DayCount INT)

WHILE @WeekDay <8
BEGIN
    WHILE @dtStart <= @dtEnd
    BEGIN
         IF DATEPART(WEEKDAY,@dtStart)=@WeekDay INSERT INTO @t SELECT @dtStart
         SET @dtStart=DATEADD(DAY,1,@dtStart)
    END

    --SELECT * FROM @t
    INSERT INTO @WeekDayCount
    SELECT @WeekDay,NULL,COUNT(*)
    FROM @t

    DELETE FROM @T

    SET @WeekDay=@WeekDay-1

    IF(@WeekDay<1) BREAK

    SET @dtStart=DATEADD(mm,DATEDIFF(mm,0,dateadd(month,-1,getdate())),0)--上月第一 
    SET @dtEnd=dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))--上月最后一

END

UPDATE A SET A.WeekDayEnglish=
(
    SELECT B.WeekDayEnglish   
    FROM 
        (
            SELECT 1 AS WeekDay,'Sunday' AS WeekDayEnglish
            UNION ALL SELECT 2 AS WeekDay,'Monday' AS WeekDayEnglish
            UNION ALL SELECT 3 AS WeekDay,'Tuesday' AS WeekDayEnglish
            UNION ALL SELECT 4 AS WeekDay,'Wednesday' AS WeekDayEnglish
            UNION ALL SELECT 5 AS WeekDay,'Thursday' AS WeekDayEnglish
            UNION ALL SELECT 6 AS WeekDay,'Friday' AS WeekDayEnglish
            UNION ALL SELECT 7 AS WeekDay,'Saturday' AS WeekDayEnglish
        ) B
    WHERE B.WeekDay=A.WeekDay
)
FROM @WeekDayCount A

SELECT * FROM @WeekDayCount

 /*

 DATEPART(WEEKDAY,@dtStart)=1  表示周日，星期天
 DATEPART(WEEKDAY,@dtStart)=4  表示周三

  WeekDay     WeekDayEnglish DayCount
----------- -------------- -----------
7           Saturday       4
6           Friday         4
5           Thursday       4
4           Wednesday      4
3           Tuesday        5
2           Monday         5
1           Sunday         4

(7 行受影响)

    */




select datediff(dd,getdate(),dateadd(mm,1,getdate())) -- 计算当月共有多少  

--select DATEADD(mm,DATEDIFF(mm,0,dateadd(month,-1,getdate())),0)--上月第一   
--select dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))--上月最后一  

select datediff(dd
      ,DATEADD(mm,DATEDIFF(mm,0,dateadd(month,-1,getdate())),0) 
      ,dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))  
    ) + 1     -- 计算上个月共有多少天

📱 扫码关注公众号

扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满