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