I often need to know if a date that I'm working with is a week day or a weekend. While T-SQL has some functions that help with that, you need to use a combination of them to get a reliable outcome that's independent of the current session settings. (Based on Saturday / Sunday being weekends but easy for you to change if you have different weekends). To make it easy, we added two functions IsWeekday and IsWeekend to our free SDU Tools for developers and DBAs.
The functions are simple. They take a single parameter @InputDate and they return a bit value which is 1 if the function is returning true.
You can see them in action in the main image above, and in the video here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
http://sdutools.sqldownunder.com
SELECT d.theDate,
DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend
FROM (
SELECT CAST('20081124' AS SMALLDATETIME) AS theDate UNION ALL
SELECT '20081125' UNION ALL
SELECT '20081126' UNION ALL
SELECT '20081127' UNION ALL
SELECT '20081128' UNION ALL
SELECT '20081129' UNION ALL
SELECT '20081130'
) AS d
Cool. Pretty similar for the core function actually.
CASE WHEN DATEDIFF(day, '20000101', ISNULL(@InputDate, SYSDATETIME())) % 7 BETWEEN 2 AND 6
THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END;