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

最新推荐文章于 2024-09-22 13:48:15 发布

清海扬波

最新推荐文章于 2024-09-22 13:48:15 发布

阅读量1w

点赞数

文章标签：工作 sql

本文提供了两种使用SQL来判断指定日期是否为周末的方法。第一种方法通过计算日期与基准日期之间的天数差来确定。第二种方法则利用DATEPART函数获取星期几的数值并根据该数值判断是否为周末。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方法一：

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