Find the Same day of Previous Year Given by Current Year Date in SQL Server

Question

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.

Suppose 2014-03-06 is Today Date and Day is Thursday I want to Find the Same day in Previous lies in the same week .which is 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

Answer 1

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:

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