判断是工作日还是周末的SQL语句

判断是工作日还是周末的SQL语句

方法一：
 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
方法二:
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

select count(id),sum(case when day='星期一' or day='星期二' or day='星期三' or day='星期四' or day='星期五' then 1 else 0 end) 工作日,sum(case when day='星期六' or day='星期日' then 1 else 0 end) 周末 from table 另外：我发现你的表中有重复值啊，这个怎么算，不管么？还是要去重？这张表（我说的是例子）似乎有点问题。 还应该由其他写法，暂时就想起这一种来。