sql周日
❶ sql怎麼查詢當前周的第一天（周一），跟當前周的最後一天（周日）
select convert(varchar(10),DATEADD(dd, -(CASE when datepart(weekday,'2018-02-25') = 1 Then 8 ELSE datepart(weekday,'2018-02-25')END -2), '2018-02-25'),120)as "第一天(周一)",
convert(varchar(10),DATEADD(dd, (8-CASE when datepart(weekday,'2018-02-25') = 1 Then 8 ELSE datepart(weekday,'2018-02-25')END), '2018-02-25'),120)as "最後一天(周日)"
❷ sql計算上周日的日期怎麼寫
select trunc(next_day(sysdate,'sunday')-7) from al;
❸ 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中計算周六,周日天數
先建立函數，再執行下面的語句
CREATE FUNCTION getday --
(@datestart datetime,--啟始日期
@dateend datetime, --結束日期
@DW VARCHAR(6)) --星期幾
RETURNS INT
AS
BEGIN
DECLARE @DAYCOUNT int
SET @DAYCOUNT=0
while @datestart<=@dateend
begin
if datename(dw,@datestart)=@DW
set @DAYCOUNT=@DAYCOUNT+1
SET @DATESTART=DATEADD(DAY,1,@DATESTART)
end
RETURN(@DAYCOUNT)
END
GO
SELECT DBO.GETDAY('2008-01-01','2008-12-31','星期六') AS SATDAY,
DBO.GETDAY('2008-01-01','2008-12-31','星期日') AS SUNDAY
❺ 怎麼使用sql語句查詢日期所在周的一周各天
檢索日期所在周的一周各天日期方法
一、用到的函數有datepart(),dateadd()
1、datepart()函數，返回代表指定日期的指定日期部分的整數。
語法：DATEPART(datepart,date)
參數：datepart
是指定應返回的日期部分的參數。參數如下
❻ sql日期處理，如何根據所查日期得出所在周
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)--所在周的周一
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),6)--所在周的周日
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)周一,DATEADD(wk,DATEDIFF(wk,0,getdate()),6)周日
周一 周日
2014-11-1700:00:00.000 2014-11-2300:00:00.000
❼ 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語句怎麼查周六周天
比如，2010年07月31日是星期六，你就拿這個日期，減去你要判斷的日期，然後在看看結果是否能被7整除就可以了。這樣寫能查出所有周六的日期：(假設表名為： 表1，有日期欄位) select 日期 from 表1 where ( {^2010-07-31} - 日期 )%7=0
❾ 在sql語句中如何判斷周六日
周六是
datepart(week,getdate())=6
周日是
datepart(week,getdate())=7
select case when datepart(week,getdate()) in (6,7) then '休息日' else '工作日' end
❿ sql如何按周一、周二。。。周日 分組查詢
select sum(cost),w1 from
(select cost,datepart(w,date1) as w1 from tb1 where datediff(day,'2015-6-1',date1)>=0
and datediff(day,date1,'2015-6-30')>=0) as a1
group by w1