Monday, July 13, 2009

No of Weekdays in a year

I posted this over at MSDN T-SQL forum

How to find number of weekdays in a year.

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









No comments:

Post a Comment