Easy script for calculating weekday or weekend for a date

Wed Jan 14, 2009 by Peter Larsson in sql-server-2008, algorithms, sql-server-2005, sql-server-2000

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