Thinking outside the box

# 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

# 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.

# 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?

# 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.

# 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