Return data 2 years back same month previous week with date range-CodePudding

I am trying to extract 2 years ago data with date range greater than 07/01/2019 and 2 years ago same month and week. Need suggestions on date conversion as well

select 
    tilr.BusinessUnitID
   ,emph.employeeID
   ,convert(varchar(10), cast(cast(tilr.date_key as varchar(10)) as date), 101) as ConvertDate
   ,tilr.paidhr as 'Paid hr'
from [dbo].[location] tilr
inner join [dbo].[Employee] emph
    on emph.employeeID = tilr.employeeID
    and emph.businessunitid = tilr.BusinessUnitID
    and emph.date_key = tilr.date_key
where 
    
tilr.date_key >= 20190701

and datename(year, convert(varchar(10), cast(cast(tilr.date_key as varchar(10))as date), 101)) 
< DateAdd(YY, -2, GETDATE())

Trying to get data for date range >= 07/01/2019 and < 10/23/2019 ( /- days of same month 2 years ago) for comparison. With above query I'm getting data till end of the year 12/2019 instead of 10/2019.

Sample data

BusinessUnitID employeeID  ConvertDate Paid hr  
1234             1          07/01/2019   1.4
2345             2          10/25/2019   3.5

CodePudding user response：

It looks like you need something like the following condition

tilr.date_key >= DATEADD(month, -2, DATEFROMPARTS( YEAR(GETDATE()) - 2, MONTH(GETDATE()), 1 )) AND
tilr.date_key <  DATEADD(month, 1, DATEFROMPARTS( YEAR(GETDATE()) - 2, MONTH(GETDATE()), 1 ))

Note how calculations only use date functions, not conversion to/from varchar, and that there are no functions on the actual column value. This means that indexes can be used efficiently.