I was reminded of a SQL function to determine if a date was a weekday or a weekend I wrote a while back when I saw the requirements of a project a colleague was working on. You'll see this requirement fairly often in many business applications. A company might want to span certain activities over business or working days only. The requirement might be to only include business days in certain calculations. Either way, there are a few things to keep in mind when making this calculation.
Making the calculation can be very simple. It is easy code to write. However, in order to make sure the calculation works with the server's regional settings in mind you need to understand how the date settings effect the SQL date functions. A great example of the role that regional settings play in the results of the SQL date functions is outlined in this Informit.com article by Baya Pavliashvili:
The first day of the week can vary from country to country. In the US the week begins on Sunday, which is day 7 according to SQL server). Some calendars begin the week on Monday (which is day 1 according to SQL). However, getting the weekday datepart will return Sunday as 1 and Saturday as 7. The problem comes into play with international regional settings, where Sunday is not the start of the week, because the @@DATEFIRST setting in SQL is changed according to the calendar used, which then will effect the result from getting the weekday datepart (since it returns the value based on the @@DATEFIRST value). The solution to this is to take the @@DATEFIRST value into account in your calculation. As you can see from the article mentioned above that took a short trivial piece of code and turned it into a long drawn-out function.
I don't like long code when it could be shortened. Sure it might be easier to read, but I like to keep things short & compact. I take the @@DATEFIRST value minus 1 and add that to the weekday datepart minus 1. Then if that result modulus 7 equals either 5 or 6 then you have a weekend (Saturday or Sunday).
create function fn_IsWeekDay
(
@date datetime
)
returns bit
as
begin
declare @dtfirst int
declare @dtweek int
declare @iswkday bit
set @dtfirst = @@datefirst - 1
set @dtweek = datepart(weekday, @date) - 1
if (@dtfirst + @dtweek) % 7 not in (5, 6)
set @iswkday = 1 --business day
else
set @iswkday = 0 --weekend
return @iswkday
end
Short and sweet. Now you can simply do this:
if dbo.fn_IsWeekDay(@date) = 1
begin
--do some magic here ;-)
end
--or
select a.SomeFieldsForCalculation from table a
where dbo.fn_IsWeekDay(a.SomeDateField) = 1
and all is good.