No of weekdays in a year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

No of weekdays in a year

Author

Topic

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-12 : 04:25:15

i posted this in MSDN Transact-SQL Forum thought i replicate it here.

1 year = 365 or 366 days = 52 week
1 week = 5 week days

so minimum there will be 52 x 5 week days in a year. So basically only need to determine the 365th day or 366th day of the year is it a week day. This is handle by the 2 case statements in the query

DECLARE	@YEAR	int,
	@boyear	datetime,
	@eoyear	datetime

SELECT	@YEAR	= 2009
SELECT	@boyear	= DATEADD(YEAR, @YEAR - 1900, 0),
	@eoyear	= DATEADD(YEAR, @YEAR - 1900 + 1, -1)

SELECT 	weekdays_year	= (52 * 5) 
			+ CASE 	WHEN 	DATEADD(DAY, (52 * 7), @boyear) <> @eoyear
				AND	DATENAME(weekday, DATEADD(DAY, (52 * 7), @boyear)) NOT IN ('Saturday', 'Sunday')
				THEN	1
				ELSE	0
				END
			+ CASE  WHEN 	DATENAME(weekday, @eoyear) NOT IN ('Saturday', 'Sunday') 
				THEN 	1 
				ELSE 	0 
				END

-- Verify with MVJ's F_TABLE_DATE
SELECT	[1st DAY of the YEAR] 	= @boyear, 
	[Last DAY of the YEAR]	= @eoyear, 
	[No of Week Days]	= COUNT(*)
FROM	F_TABLE_DATE (@boyear, @eoyear)
WHERE	[WEEKDAY_NAME]	NOT IN ('Sat', 'Sun')

Anybody have better query ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 06:24:44

Haven't tested for performance yet, but it is language independant.
There is however built-in error checking for invalid year numbers.

CREATE FUNCTION dbo.fnWeekDays
(
	@Year SMALLINT
)
RETURNS SMALLINT
AS
BEGIN
	RETURN	(
			SELECT	260 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)
			FROM	(
					SELECT	DATEADD(YEAR, @Year - 1899, -1) AS dt

					UNION ALL
					
					SELECT	DATEADD(YEAR, @Year - 1899, -2)
					WHERE	ISDATE(10000 * @Year + 229) = 1 
				) AS d
			WHERE	@Year BETWEEN 1753 AND 9999
		)
END

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 07:47:13

And here is number of weeksdays in a month

CREATE FUNCTION dbo.fnMonthWeekDays
(
	@Year SMALLINT,
	@Month TINYINT
)
RETURNS TINYINT
AS
BEGIN
	RETURN	(
			SELECT	20 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)
			FROM	(
					SELECT	DATEADD(MONTH, 12 * @Year - 22800 + @Month, -1) AS dt
					WHERE	ISDATE(10000 * @Year + 100 * @Month + 31) = 1

					UNION ALL

					SELECT	DATEADD(MONTH, 12 * @Year - 22800 + @Month, -2)
					WHERE	ISDATE(10000 * @Year + 100 * @Month + 30) = 1

					UNION ALL

					SELECT	CASE ISDATE(10000 * @Year + 100 * @Month + 29)
							WHEN 1 THEN DATEADD(MONTH, 12 * @Year - 22800 + @Month, -3)
							ELSE '18991231'
						END
				) AS d
			WHERE	@Year BETWEEN 1753 AND 9999
				AND @Month BETWEEN 1 AND 12
		)
END

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 08:27:05

Or for any given period!

CREATE FUNCTION dbo.fnPeriodWeekdays
(
	@StartDate DATETIME,
	@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
	RETURN	(
			DATEDIFF(DAY, @StartDate, @EndDate) + 1
			+ (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2
			- (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2
			- CASE DATEDIFF(DAY, '17530101', @StartDate) % 7
				WHEN 6 THEN 1
				ELSE 0
			END 
			- CASE DATEDIFF(DAY, '17530101', @EndDate) % 7
				WHEN 5 THEN 1
				ELSE 0
			END
		)
END

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-12 : 08:48:21

3 scripts and you are suppose to be on vacation ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 08:50:05

Yes. All my girls are taking their afternoon sleep, so I have some time left for SQLTeam.
It's very hot here today, so an afternoon nap is very needed.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources