Peter Larsson Blog

Patron Saint of Lost Yaks

Easy script for calculating weekday or weekend for a date

SELECT d.theDate,
        DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend,
        1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday
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

Legacy Comments


Uri Dimant
2009-01-14
re: Easy script for calculating weekday or weekend for a date
Hi Peter. Thanks for the script
I think we should pay attention to the DATEFIRST
settings. For example, here in Israel we start the week from Sunday (7) :-)))

SELECT d.theDate,
CASE WHEN DATEPART(weekday,thedate+@@DATEFIRST -1)<6 THEN 1 ELSE 0 END,
CASE WHEN DATEPART(weekday,thedate+@@DATEFIRST -1)>=6 THEN 1 ELSE 0 END

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

Peso
2009-01-14
re: Easy script for calculating weekday or weekend for a date
A good tip.
As a rule of thumb I avoid to rely on system settings, because you may never know when they change.
In this case it is fairly simple function.

Joe Celko
2009-01-15
re: Easy script for calculating weekday or weekend for a date
Wouldn't a Calendar table that can handle long weekends and holidays be better than keeping your mindset stuck in procedural code?

Peso
2009-01-15
re: Easy script for calculating weekday or weekend for a date
Most probably.
As with other things, it depends on what you are going to use it for.

Having a large calendar table would increase you read count for the query. The code above can be made inline.

I thought I should show a way that was language independent, and setting-independent.

Log Buffer
2009-01-16
re: Easy script for calculating weekday or weekend for a date
Elsewhere, Thinking outside the box has an easy script for calculating weekday or weekend for a date.

Log Buffer #131