SQL -从机器中选取当前日期并进行比较
SQL -从机器中选取当前日期并进行比较
提问于 2016-09-06 16:22:05
对于下面的查询场景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而不是硬编码日期。我想从机器中选择当前日期并进行比较。
一周从星期天开始，星期六结束。有什么需要帮忙的吗？
回答 5
Stack Overflow用户
发布于 2016-09-06 16: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的一点帮助
Stack Overflow用户
发布于 2016-09-06 16: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()))Stack Overflow用户
发布于 2016-09-06 16: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]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39344038
复制相关文章
相似问题
腾讯云开发者
