SQL Server Get data for only the past year

Question

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   SELECT     FROM     WHERE date  gt   8 27 2007 12 00 00 AM

User · Answer

The most readable  IMO   SELECT   FROM TABLE WHERE Date  gt     DATEADD yy  -1  CONVERT datetime  CONVERT varchar  GETDATE    101      Which    Gets now s datetime GETDATE      8 27 2008 10 23am  Converts to a string with format 101 CONVERT varchar   8 27 2008 10 23am   101     8 27 2007  Converts to a datetime CONVERT datetime   8 27 2007      8 27 2008 12 00AM  Subtracts 1 year DATEADD yy  -1   8 27 2008 12 00AM      8 27 2007 12 00AM    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

User · Answer

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   SELECT     FROM     WHERE YEAR DATE    YEAR GETDATE    - 1   Then if you want to restrict this query  you can add some other filter  but always searching in the last year   SELECT     FROM     WHERE YEAR DATE    YEAR GETDATE    - 1 AND DATE  gt   05 05 2007

User · Answer

GETDATE   returns current date and time  If last year starts in midnight of current day last year  like in original example  you should use something like  DECLARE  start datetime SET  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 SELECT column1  column2       columnN FROM table WHERE date  gt    start

User · Answer

For some reason none of the results above worked for me    This selects the last 365 days    SELECT     From     WHERE date BETWEEN CURDATE   - INTERVAL 1 YEAR AND CURDATE

User · Answer

argument for DATEADD function    DATEADD   datepart     number     date      datepart can be  yy  qq  mm  dy  dd  wk  dw  hh  mi  ss  ms  number is an expression that can be resolved to an int that is added to a datepart of date  date is an expression that can be resolved to a time  date  smalldatetime  datetime  datetime2  or datetimeoffset value

User · Answer

Look up dateadd in BOL  dateadd yy -1 getdate

User · Answer

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   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   SELECT      FROM      WHERE year  your date column     year DATEADD year -1 getdate       Thanks to those above whose solutions helped me arrive at what I needed

User · Answer

The other suggestions are good if you have  SQL only    However I suggest  that - if possible - you calculate the date in your program and insert it as string in the SQL query   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

User · Answer

declare  iMonth int declare  sYear varchar 4  declare  sMonth varchar 2  set  iMonth   0 while  iMonth  gt  -12 begin     set  sYear   year DATEADD month  iMonth GETDATE         set  sMonth   right  0  cast month DATEADD month  iMonth GETDATE     as varchar 2   2      select  sYear    sMonth     set  iMonth    iMonth - 1 end

User · Answer

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   My query is a bit different in that it works for the prior year up to and including the current date   SELECT      FROM      WHERE year date   gt  year DATEADD year  -2  GETDATE      For example  on Feb 17  2017 this query returns results from 1 1 2016 to 2 17 2017

User · Answer

The following adds -1 years to the current date   SELECT     From     WHERE date  gt  DATEADD year -1 GETDATE

User · Answer

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    SELECT     FROM     WHERE  CONVERT datetime REPLACE LEFT LTRIM  MoYr   2   -         01   RIGHT RTRIM  MoYr   4    gt    DATEADD year -1 GETDATE

[sql] SQL Server: Get data for only the past year

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql