SQL syntax for Same Date Last Year

Question

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? JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

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. More common date routines.