在 SQL Server 中查找按当前年份日期给定的上一年的同一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22223156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find the Same day of Previous Year Given by Current Year Date in SQL Server
提问by DareDevil
I am working with SQL Server, The scenario is to find out the Same Day's Date of Previous Year as of Today's Day.
我正在使用 SQL Server，该方案是找出与今天相同的前一年的同一天日期。
Suppose 2014-03-06is Today Date and Day is ThursdayI want to Find the Same day in Previous lies in the same week .which is 2013-03-07
假设2014-03-06是今天的日期，日期是星期四我想在以前的同一天中找到同一天位于同一周。即2013-03-07
can any body help? HERE is what i Have Written:
任何身体都可以帮忙吗？这是我写的：
DECLARE @DateFrom AS DATETIME
DECLARE @DateTo AS DATETIME
SET @DateFrom = '2014-01-01'
SET @DateTo = '2014-02-10'
DECLARE @Count AS INT
SET @Count = DATEDIFF(DAY, @DateFrom, @DateTo)
CREATE TABLE #current_year /*This Year*/
(
[Date] DATETIME ,
WeekNum INT,
[Day] VARCHAR(20),
Data INT
)
CREATE TABLE #last_year /*This Year -1*/
(
[Date] DATETIME ,
WeekNum INT,
[Day] VARCHAR(20),
Data INT
)
WHILE ( @Count > 0 )
BEGIN
INSERT INTO #current_year
VALUES ( CONVERT(VARCHAR(10), @DateFrom, 101),
DATEPART(week,@DateFrom),
DATENAME(weekday, @DateFrom),@Count)
INSERT INTO #last_year
VALUES ( CONVERT(VARCHAR(10), DATEADD(YEAR, -1, @DateFrom), 101),
DATEPART(week,DATEADD(YEAR,1,@DateFrom)),
DATENAME(weekday, DATEADD(YEAR, -1, @DateFrom)),@Count)
SET @DateFrom = DATEADD(day, 1, @DateFrom)
SET @Count = @Count - 1
END
SELECT * from #current_year
SELECT * from #last_year
SELECT CONVERT(varchar(10),#current_year.[Date],111) AS CYDate,
--ISNULL(CONVERT(varchar(10),#last_year.[Date],111) ,/*CONVERT(varchar(10),DateAdd(dd, 1, DATEADD(yy, -1, #current_year.Date)),111)*/) AS LYDate
--CONVERT(varchar(10),#last_year.[Date],111) AS LYDate
Coalesce(CONVERT(varchar(10),#last_year.[Date],111) ,DateAdd(dd, 1, DATEADD(yy, -1, #current_year.Date))) AS LYDate,
#current_year.Data AS CD,
#last_year.Data AS LD
FROM #current_year
--LEFT JOIN #last_year ON #last_year.WeekNum = #current_year.WeekNum
-- AND #last_year.[Day] = #current_year.[Day]
Left JOIN #last_year ON #last_year.WeekNum = DatePart(wk, GETDATE())
DROP TABLE #current_year
DROP TABLE #last_year
Here is the Output: 
这是输出：
Here is the output after adding your solution, now in left join it excludes (NULL) data of previous year
这是添加解决方案后的输出，现在在左连接中它排除了上一年的（NULL）数据
回答by Lanorkin
Basically you need to find difference in days between same dates in this and previous years, then understand "day difference" by mod 7, and sum it with date in previous year:
基本上，您需要找到今年和前几年相同日期之间的天数差异，然后通过 mod 7 理解“天数差异”，并将其与上一年的日期相加：
DECLARE @now DATETIME
SET @now = '2014-03-06'
SELECT CAST (DATEADD(YEAR, -1, @now) + (CAST (@now as INT) - CAST (DATEADD(YEAR, -1, @now) AS INT)) % 7 AS DATE)
Returns
退货
2013-03-07
回答by MusicLovingIndianGirl
Try
尝试
DECLARE @now Date
SET @now = '2014-06-03'
SELECT DATEADD(week, -52, @now)
回答by Suraj Shrestha
SELECT DateName(dw, DATEADD(yy, -1, GETDATE()))
gives Wednesday
给 Wednesday
SELECT DateName(dw, DateAdd(dd, 1, DATEADD(yy, -1, GETDATE())))
gives Thursday
给 Thursday
edit:
编辑：
SELECT DateAdd(dd, 1, DATEADD(yy, -1, GETDATE()))
gives '2013-03-07 17:30:16.590'
给 '2013-03-07 17:30:16.590'
you need to cast the date as per you requirement..
您需要根据您的要求投射日期..
update:change this part with,
更新：改变这部分，
Left JOIN #last_year ON #last_year.WeekNum = DatePart(wk, GETDATE())
in your case:
在你的情况下：
Left JOIN #last_year ON DatePart(wk, #last_year.[Date]) = DatePart(wk, #current_year.[Date])
update 2:
更新2：
Left JOIN #last_year ON (MONTH(#last_year.[Date])=MONTH(#current_year.[Date]) and Day(#last_year.[Date])=Day(#current_year.[Date]))
Output:
输出：
or
或者
output:
输出：
Left JOIN #last_year ON (#last_year.WeekNum = #current_year.WeekNum and #last_year.[Day] = #current_year.[Day])
choose which ever is your required output.
选择您需要的输出。
回答by mehdi lotfi
DECLARE @Date DATE = '2014-03-06'
SELECT DATEADD(WEEK,-52,@Date)
Retrun value :
返回值：
2013-03-07
2013-03-07
