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

最新推荐文章于 2022-05-23 19:37:19 发布

ijwsoft

最新推荐文章于 2022-05-23 19:37:19 发布

阅读量3.7k

点赞数

分类专栏： Sql Server

本文链接：https://blog.csdn.net/keenweiwei/article/details/40544091

版权

Sql Server 专栏收录该内容

125 篇文章 1 订阅

订阅专栏

-- 取上个月所有的工作日对应的天数
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     -- 计算上个月共有多少天