I am using SQL Server 2014 and I have the following T-SQL query which pulls data from a View Table:
SELECT * from MyView
WHERE StayDate >= '2014-07-01'
I need to add a filter to this query that will be applied on a field called "CreatedOn" (it is datetime field). The filter needs to filter the "CreatedOn" based on the SAME DATE LAST YEAR.
Thus, if today's date is '2015-10-26', my query should look this:
SELECT * from MyView
WHERE StayDate >= '2014-07-01'
AND CreatedOn <= '2014-10-26'
Since this query will be used in a PowerPivot environment, I am thinking along these lines:
SELECT * from MyView
WHERE StayDate >= '2014-07-01'
AND CreatedOn <= getdate()
How do I modify the getdate() part so that it becomes the Same Date Last Year?
To get the date a year before the current date, you could use:
DATEADD(YEAR, -1, GETDATE())
However, since that includes the time component, there's a possibility that some records will be left out. You should use this instead:
< DATEADD(DAY, 1, DATEADD(YEAR, -1, DATEDIFF(DAY, '19000101', GETDATE())))
The above will return the date a year before the current plus one day. That is, if today's date is '2015-10-26', the above will return '2014-10-27'. Note that this will be without a time component and you should be using < for the comparison.