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