取上个月所有的工作日对应的天数代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】取上个月所有的工作日对应的天数

作者:keenweiwei / 发布于2015/3/24/ 953


	-- 取上个月所有的工作日对应的天数
	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 行受影响)
	*/

试试其它关键字

　对应的天数　　工作日　

同语言下

可能有用的

keenweiwei贡献的其它代码(15)