sql周末

⑴ sql语句怎么查周六周天

比如，2010年07月31日是星期六，你就拿这个日期，减去你要判断的日期，然后在看看结果是否能被7整除就可以了。这样写能查出所有周六的日期：(假设表名为： 表1，有日期字段) select 日期 from 表1 where ( {^2010-07-31} - 日期 )%7=0

⑵ 怎么使用sql语句查询日期所在周的一周各天

检索日期所在周的一周各天日期方法

一、用到的函数有datepart(),dateadd()

1、datepart()函数，返回代表指定日期的指定日期部分的整数。

语法：DATEPART(datepart,date)

参数：datepart

是指定应返回的日期部分的参数。参数如下

⑶ sql语句，只去除周六周日，不用管节日

1. table_name要换成你的表名:
select adddate(xxdate, 20) as new_date
from table_name
where dayofweek(new_date) <> 1 and dayofweek(new_date) <> 7;
对于DAYOFWEEK()函数，返回值: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.
2. 需要你先创建这么一列日期，然后统计天数的时候排出周末就可以了。
select count(new_date)
from table_name
where dayofweek(new_date) <> 1 and dayofweek(new_date) <> 7;

⑷ SQL统计双休日

select Inspect_name,
A=sum(A), B=sum(B),双休日=sum(case when 双休日 >= 2 then 1 else 0 end)
from
(
select weekly,Inspect_name,
A=sum(A), B=sum(B),双休日=sum(case when 双休日>119 then 1 else 0 end)
from
(
select weekly, Inspect_name, vdatetime2,
sum(case when rank= 'A' then 1 else 0 end) A,
sum(case when rank= 'B' then 1 else 0 end) B,
SUM(CASE WHEN xxx = '休' THEN vtime ELSE 0 END) AS 双休日
from
(
select datepart(wk,(case when convert(varchar(16),vdatetime2,120) between convert(varchar(10),vdatetime2,120)+' 00:00' and convert(varchar(10),vdatetime2,120)+' 05:01' then vdatetime2 -1
else vdatetime2 end)) as weekly,Inspect_name, rank,
(case when convert(varchar(16),vdatetime2,120) between convert(varchar(10),vdatetime2,120)+' 00:00' and convert(varchar(10),vdatetime2,120)+' 05:01' then convert(varchar(10),vdatetime2 -1,120)
else convert(varchar(10),vdatetime2,120) end) as vdatetime2,
( CASE WHEN(datepart(dw, vdatetime2)>=6 AND CONVERT(VARCHAR(8),vdatetime2,108)>'23:00') OR (datepart(dw, vdatetime2)=7 )or (datepart(dw, vdatetime2)=1 ) OR(datepart(dw, vdatetime2)=2 AND CONVERT(VARCHAR(8),vdatetime2,108) <'04:59')THEN '休' ELSE '不休' END) as 'xxx',
vtime
from tb
) t
group by weekly,Inspect_name, vdatetime2
) Tbl
group by weekly,Inspect_name
) tbl1
group by Inspect_name
DATEPART(ww,DATE) 函数计算的周次讲解：

这个函数是以周日为每个周次的第一天，所以你要实现周一为每周的开始必须这样书写才能实现
DATEPART(ww,DATE-1)

⑸ 判断是工作日还是周末的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