<p>I am writing a query in which I have to get the data for only the last year. What is the best way to do this?</p>

<pre><code>SELECT ... FROM ... WHERE date &gt; '8/27/2007 12:00:00 AM'
</code></pre>

[{'answer_id': 30229, 'author': 'samjudson', 'author_id': 1908, 'author_profile': 'https://Stackoverflow.com/users/1908', 'pm_score': 9, 'selected': True, 'text': '<p>The following adds -1 years to the current date:</p>\n\n<pre><code>SELECT ... From ... WHERE date &gt; DATEADD(year,-1,GETDATE())\n</code></pre>\n'}, {'answer_id': 30232, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 3, 'selected': False, 'text': '<p>Look up dateadd in BOL</p>\n\n<pre><code>dateadd(yy,-1,getdate())\n</code></pre>\n'}, {'answer_id': 30252, 'author': 'Grzegorz Gierlik', 'author_id': 1483, 'author_profile': 'https://Stackoverflow.com/users/1483', 'pm_score': 2, 'selected': False, 'text': "<p><code>GETDATE()</code> returns current date <strong>and time</strong>.</p>\n<p>If <em>last year</em> starts in midnight of current day last year (like in original example) you should use something like:</p>\n<pre><code>DECLARE @start datetime\nSET @start = dbo.getdatewithouttime(DATEADD(year, -1, GETDATE())) -- cut time (hours, minutes, ect.) --  getdatewithouttime() function doesn't exist in MS SQL -- you have to write one\nSELECT column1, column2, ..., columnN FROM table WHERE date &gt;= @start\n</code></pre>\n"}, {'answer_id': 30268, 'author': 'Ivan Bosnic', 'author_id': 3221, 'author_profile': 'https://Stackoverflow.com/users/3221', 'pm_score': 3, 'selected': False, 'text': "<p>Well, I think something is missing here. User wants to get data from the last year and not from the last 365 days. There is a huge diference. In my opinion, data from the last year is every data from 2007 (if I am in 2008 now). So the right answer would be:</p>\n\n<pre><code>SELECT ... FROM ... WHERE YEAR(DATE) = YEAR(GETDATE()) - 1\n</code></pre>\n\n<p>Then if you want to restrict this query, you can add some other filter, but always searching in the last year.</p>\n\n<pre><code>SELECT ... FROM ... WHERE YEAR(DATE) = YEAR(GETDATE()) - 1 AND DATE &gt; '05/05/2007'\n</code></pre>\n"}, {'answer_id': 30273, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 3, 'selected': False, 'text': "<p>The most readable, IMO:</p>\n\n<pre><code>SELECT * FROM TABLE WHERE Date &gt;\n   DATEADD(yy, -1, CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)))\n</code></pre>\n\n<p>Which:</p>\n\n<ol>\n<li>Gets now's datetime <em>GETDATE() = #8/27/2008 10:23am#</em></li>\n<li>Converts to a string with format 101 <em>CONVERT(varchar, #8/27/2008 10:23am#, 101) = '8/27/2007'</em></li>\n<li>Converts to a datetime <em>CONVERT(datetime, '8/27/2007') = #8/27/2008 12:00AM#</em></li>\n<li>Subtracts 1 year <em>DATEADD(yy, -1, #8/27/2008 12:00AM#) = #8/27/2007 12:00AM#</em></li>\n</ol>\n\n<p>There's variants with DATEDIFF and DATEADD to get you midnight of today, but they tend to be rather obtuse (though slightly better on performance - not that you'd notice compared to the reads required to fetch the data).</p>\n"}, {'answer_id': 30406, 'author': 'BlaM', 'author_id': 999, 'author_profile': 'https://Stackoverflow.com/users/999', 'pm_score': 0, 'selected': False, 'text': '<p>The other suggestions are good if you have "SQL only".</p>\n\n<p>However I suggest, that - <strong>if possible</strong> - you calculate the date in your program and insert it as string in the SQL query.</p>\n\n<p>At least for for big tables (i.e. several million rows, maybe combined with joins) that will give you a considerable speed improvement as the optimizer can work with that much better.</p>\n'}, {'answer_id': 7501550, 'author': 'imtheref', 'author_id': 949377, 'author_profile': 'https://Stackoverflow.com/users/949377', 'pm_score': 0, 'selected': False, 'text': '<p>argument for DATEADD function :</p>\n\n<pre><code>DATEADD (*datepart* , *number* , *date* )\n</code></pre>\n\n<p><em>datepart</em> can be: yy, qq, mm, dy, dd, wk, dw, hh, mi, ss, ms</p>\n\n<p><em>number</em> is an expression that can be resolved to an int that is added to a datepart of date</p>\n\n<p><em>date</em> is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.</p>\n'}, {'answer_id': 10657540, 'author': 'Tony', 'author_id': 1404012, 'author_profile': 'https://Stackoverflow.com/users/1404012', 'pm_score': 0, 'selected': False, 'text': "<pre><code>declare @iMonth int\ndeclare @sYear varchar(4)\ndeclare @sMonth varchar(2)\nset @iMonth = 0\nwhile @iMonth &gt; -12\nbegin\n    set @sYear = year(DATEADD(month,@iMonth,GETDATE()))\n    set @sMonth = right('0'+cast(month(DATEADD(month,@iMonth,GETDATE())) as varchar(2)),2)\n    select @sYear + @sMonth\n    set @iMonth = @iMonth - 1\nend\n</code></pre>\n"}, {'answer_id': 16090603, 'author': 'D.E. White', 'author_id': 2296481, 'author_profile': 'https://Stackoverflow.com/users/2296481', 'pm_score': 4, 'selected': False, 'text': "<p>I found this page while looking for a solution that would help me select results from a prior calendar year. Most of the results shown above seems return items from the past 365 days, which didn't work for me.</p>\n\n<p>At the same time, it did give me enough direction to solve my needs in the following code - which I'm posting here for any others who have the same need as mine and who may come across this page in searching for a solution.</p>\n\n<pre><code>SELECT .... FROM .... WHERE year(*your date column*) = year(DATEADD(year,-1,getdate()))\n</code></pre>\n\n<p>Thanks to those above whose solutions helped me arrive at what I needed.</p>\n"}, {'answer_id': 42305356, 'author': 'kevinaskevin', 'author_id': 1877580, 'author_profile': 'https://Stackoverflow.com/users/1877580', 'pm_score': 1, 'selected': False, 'text': "<p>I, like @D.E. White, came here for similar but different reasons than the original question.  The original question asks for the last 365 days.  @samjudson's answer provides that.  @D.E. White's answer returns results for the prior calendar year.</p>\n\n<p>My query is a bit different in that it works for the <strong>prior year up to and including the current date:</strong></p>\n\n<p><code>SELECT .... FROM .... WHERE year(date) &gt; year(DATEADD(year, -2, GETDATE()))</code></p>\n\n<p>For example, on Feb 17, 2017 this query returns results from 1/1/2016 to 2/17/2017</p>\n"}, {'answer_id': 47314098, 'author': 'Rick Savoy', 'author_id': 2093345, 'author_profile': 'https://Stackoverflow.com/users/2093345', 'pm_score': 0, 'selected': False, 'text': "<p>I had a similar problem but the previous coder only provided the date in mm-yyyy format. My solution is simple but might prove helpful to some (I also wanted to be sure beginning and ending spaces were eliminated):</p>\n\n<pre><code>SELECT ... FROM ....WHERE \nCONVERT(datetime,REPLACE(LEFT(LTRIM([MoYr]),2),'-\n','')+'/01/'+RIGHT(RTRIM([MoYr]),4)) &gt;=  DATEADD(year,-1,GETDATE())\n</code></pre>\n"}, {'answer_id': 59638416, 'author': 'Connor', 'author_id': 9984657, 'author_profile': 'https://Stackoverflow.com/users/9984657', 'pm_score': 1, 'selected': False, 'text': '<p>For some reason none of the results above worked for me. </p>\n\n<p>This selects the last 365 days.</p>\n\n<pre><code> SELECT ... From ... WHERE date BETWEEN CURDATE() - INTERVAL 1 YEAR AND CURDATE()\n</code></pre>\n'}, {'answer_id': 72329692, 'author': 'lloydyu24', 'author_id': 7342548, 'author_profile': 'https://Stackoverflow.com/users/7342548', 'pm_score': 0, 'selected': False, 'text': "<p>Here's my version.</p>\n<pre><code>YEAR(NOW())- 1\n</code></pre>\n<p>Example:</p>\n<pre><code>YEAR(c.contractDate) =  YEAR(NOW())- 1\n</code></pre>\n"}, {'answer_id': 72856149, 'author': 'Gabriel', 'author_id': 9709981, 'author_profile': 'https://Stackoverflow.com/users/9709981', 'pm_score': 0, 'selected': False, 'text': '<p>For me this worked well</p>\n<p>SELECT DATE_ADD(Now(),INTERVAL -2 YEAR);</p>\n'}]
['https://Stackoverflow.com/questions/30222', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2486/']