1

I have a table like this:

ID|Date    |Name|Score  
1 |1/1/2014|Erin|20.2  
2 |6/3/2014|Erin|21.2  
3 |7/2/2015|Emil|34.2    
4 |1/1/2016|Erin|20.2  
5 |2/2/2011|Paul|20.2  
6 |1/1/2012|John|54.2  
7 |1/1/2017|John|54.2  
8 |12/31/2016|Erin|23.2

I want the data back 5 years based on the year I provide.
I am using this:

select * from tblStdnt where date > DATEADD(Year,DATEDIFF(year,0, '2016')- 5, 0) 

But this gives me data starting from 2017. How do I fix this? any ideas?

It should give me:

all data from 2011 to 2016 (the 5 year period, regardless of the month or day)

UPDATE
DBFiddle link

  • your query as written should return the entire sample table. Are you saying the query does not return all results with a date greater than 2011-01-01? – Bob Klimes Dec 14 '17 at 16:28
  • It is, but it is giving me 2017 as well. I just want 5 years before the year I provide i.e. 2016 – SQLserving Dec 14 '17 at 17:53
  • 1
    Which version of SQL Server? – ypercubeᵀᴹ Dec 14 '17 at 18:02
  • 1
    2011 to 2016 is a 6 year period, by the way ;) – ypercubeᵀᴹ Dec 14 '17 at 18:59
4

Using the "magic" values of 1900 (the year of the "zero" date) and 0 (or "date zero" which is '1900-01-01') we can produce the 1st January of the two years we need (5 years ago and 1 year ahead):

SELECT *
FROM tblStdnt
WHERE DATEADD(year, 2016 - 1900 - 5, 0) 
      <= date
  AND    date < DATEADD(year, 2016 - 1900 + 1, 0) ;

In recent versions (2012+), it's easier with the DATEFROMPARTS(year, month, day) function:

SELECT *
FROM tblStdnt
WHERE DATEFROMPARTS(2016 - 5, 1, 1)
      <= date
  AND    date < DATEFROMPARTS(2016 + 1, 1, 1) ;

Note: the query - because the condition is against the column (date) and not on an expression - may* use an index on that column. Queries that have conditions with expressions on the columns (like YEAR(date) >= ..) are unlikely to use an index, unless there is an index on a computed column with the specific expression or a similar indexed view.

* "may": Since we want data that spans 6 years - so probably a large portion of the table - a table scan may still be used. Using similar conditions ("SARGable") is considered good practice though. Consider the case where the clustered index of the table is or starts with the date column. Immediately the plan can be a partial scan instead of a table scan. The YEAR(date) queries will still have to do a full table scan.

  • All the answers here work with what I need but my actual table is HUGE and yper makes a good point of SARGable queries. I found mathewb answer to be the next best in simplicity and brevity. – SQLserving Dec 15 '17 at 14:34
4

I believe that the problem you're running into is that you are only specifying a minimum date. The way your query is written, you will get everything from five years prior to the date through the end of time. You need to specify both a start and end point for your range to eliminate the data occurring after the date specified. One way to write this would be:

DECLARE @dateToEnd SMALLINT
SET @dateToEnd = 2016
SELECT  *
FROM    tblStdnt S
WHERE   YEAR(S.date) BETWEEN (@dateToEnd - 5) AND @dateToEnd

Note that this answer actually gives six full years of data - the year selected and the five preceding years.

  • I want it to be based on years. If I change one of the dates in the table to 12/31/2016 then it does not get included...I have updated the question and added a new row to show what I mean – SQLserving Dec 14 '17 at 18:13
4

You only need to use DATEADD

DECLARE @DT date = '20150101';

SELECT DATEADD(year, -5, @DT);
GO
| (No column name)    |
| :------------------ |
| 01/01/2010 00:00:00 |

dbfiddle here

UPDATE

As far as you need all data between two years you can use the next sentence:

DECLARE @DT date = '20160101';

SELECT * 
FROM   tblStdnt 
WHERE  YEAR(date) >= YEAR(@DT) - 5
AND    YEAR(date) <= YEAR(@DT);

According to your sample data:

ID | Date                | Name | Score
-: | :------------------ | :--- | :----
 1 | 01/01/2014 00:00:00 | Erin | 20.20
 2 | 31/03/2014 00:00:00 | Erin | 21.20
 3 | 07/02/2015 00:00:00 | Emil | 22.20
 4 | 31/12/2016 00:00:00 | Erin | 23.20
 5 | 02/02/2011 00:00:00 | Paul | 24.20
 6 | 31/12/2011 00:00:00 | John | 25.20
 7 | 01/01/2012 00:00:00 | John | 26.20
 9 | 01/01/2013 00:00:00 | Emil | 26.20
11 | 01/01/2016 00:00:00 | Erin | 29.20
DECLARE @DT date = '20160101';

SELECT YEAR(@DT) - 5 AS StartDate,
       YEAR(@DT) AS EndDate;
GO
StartDate | EndDate
--------: | ------:
     2011 |    2016

dbfiddle here

  • 1
    This is a better way to write this but it give the same result as OP, so probably not the issue – Bob Klimes Dec 14 '17 at 16:35
  • @McNets This gives me the same result as I am getting – SQLserving Dec 14 '17 at 18:01
1
SELECT * 
FROM tblStdnt 
WHERE YEAR(Date) <= 2016 and YEAR(DATE) > 2016-5

If it's only a small dataset that should work well enough. For larger datasets you may hit issues with the statement not being SARGable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.