Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Wednesday, January 14, 2009 2:03 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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
1/14/2009 4:53 PM | Uri Dimant
Gravatar

# 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.
1/14/2009 7:36 PM | Peso
Gravatar

# 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?
1/15/2009 12:00 AM | Joe Celko
Gravatar

# 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.
1/15/2009 8:46 AM | Peso
Gravatar

# 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
1/16/2009 9:07 PM | Log Buffer
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET