问SQL -从机器中选取当前日期并进行比较
EN

Stack Overflow用户

提问于 2016-09-06 08:22:05

回答 5查看 217关注 0票数 0

对于下面的查询场景1:本年度(前一周)-例如-第31周

sum(case when s.Date between '2016-07-17' and '2016-07-23' then s.SELLINC else 0 end) ActualSales

场景2:去年(前一周)-第31周

sum(case when s.Date between '2015-07-19' and '2015-07-25' then s.SELLINC else 0 end) LastYrVarianc

场景3:选择从当前年初到今天日期之间的日期

sum(case when s.Date between '2016-01-01' and '2016-09-05' then s.SELLINC else 0 end) YrToDateActual

场景4:选择从去年年初到去年今天的日期

sum(case when s.Date between '2015-01-01' AND '2015-09-05' then s.SELLINC else 0 end) LastYrToDateActual

而不是硬编码日期。我想从机器中选择当前日期并进行比较。

一周从星期天开始，星期六结束。有什么需要帮忙的吗？

sql

sql-server

tsql

腾讯云域名特惠，新用户首年1元起

腾讯云域名专场特惠：个人新用户.cn仅8.8元/年，企业新用户.com仅1.06元/年...

回答 5

Stack Overflow用户

发布于 2016-09-06 08:32:06

首先，GETDATE()是今天日期的SQL Server函数

数据添加(..)是将内容添加到日期的函数

1) case when s.date between DATEADD(dd,-6,getdate()) and getdate()) then...

2) case when s.date between DATEADD(yy,-1,DATEADD(dd,-6,getdate())) and DATEADD(yy,-1,getdate()) then ...

3) case when s.date between DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) and getdate() then ...

4) case when s.date between dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) and DATEADD(yy,-1,GETDATE()) then ..

来自here的一点帮助

票数 2

Stack Overflow用户

发布于 2016-09-06 08:49:53

我假设你的一周从周日开始到周六。

您可以使用此查询，

场景-1 (PreviousWeekStartDate和PreviousWeekEndDate)

s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5)

场景-2 (LastYearPreviousWeekStartDate和LastYearPreviousWeekEndDate)

s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5)

场景3 (StartOfYear和CurrentDate)

s.Date between convert(date,DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) and convert(date,getdate())

场景4 (StartOfLastYear和CurrentDateLastYear)

s.Date between convert(date,DATEADD(yy, DATEDIFF(yy, 0, dateadd(YEAR, - 1, getdate())), 0)) and convert(date,dateadd(YEAR, - 1, getdate()))

票数 1

Stack Overflow用户

发布于 2016-09-06 08:44:17

使用日期算法

declare @weekNo int = 31; 
--start of the year
declare @ys datetime = dateadd(year,datediff(year,0,getdate()),0) 
-- start of the first week of the year (may start in December of prev year)
declare @y1ws datetime = dateadd(week,datediff(week,0,@ys),0) 

select @ys, @y1ws, dateadd(week, @weekNo-1, @y1ws) [week31 start], dateadd(week, @weekNo, @y1ws) [week32 start]
--    use it this way for week 31
-- .. where somedate >= [week31 start] and somedate < [week32 start]