sql - 在 SQL Server 中查找当前年份日期给定的上一年的同一天

Question

我正在使用 SQL Server，场景是找出截至今天的上一年的同一天的日期。

假设2014-03-06是今天，日期是星期四我想在同一周的上一个谎言中查找同一天。即 2013-03-07

任何人都可以帮忙吗？这是我写的:

 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

这是输出:

这是添加解决方案后的输出，现在在左连接中它排除了前一年的(NULL)数据

Answer 1

基本上，您需要找到今年和往年相同日期之间的天数差异，然后通过 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)

返回

2013-03-07