0

I have looked through a few posts but either there is too much information or I just don't quite understand what I am looking for. eg: How to get the first and last date of the current year?

In a table I have sales orders for the last 20 years, all I want is to show the orders from January 1st of three years before until whatever the current date is.

   AND ShipDate >= (YEAR(ShipDate)-3)

Which doesn't work.

AND ShipDate >= DATEADD(YEAR,-2,GETDATE())

Which Shows exactly 2 years ago from whatever the current day is.

I want to be able to eventually create reports that show each year in the last three on their own but this is the first step and I am not doing well so far!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Lorebass
  • 42
  • 7
  • Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product (and your query used non-standard syntax). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Jan 13 '21 at 13:09
  • Some examples would make it clear what you mean by "three years before". I also added the SQL Server tag based on the code in the question. – Gordon Linoff Jan 13 '21 at 13:10
  • Does `WHERE ShipDate BETWEEN DATETIMEFROMPARTS(YEAR(CURRENT_TIMESTAMP)-3, 1, 1, 0, 0, 0, 0) AND CURRENT_TIMESTAMP` work? – Mark Moretto Jan 13 '21 at 13:21

2 Answers2

0

you were close:

AND YEAR(ShipDate) >= (YEAR(ShipDate)-3)

You were comparing a year to a date

Hogan
  • 69,564
  • 10
  • 76
  • 117
0

I want is to show the orders from January 1st of three years before until whatever the current date is.

The best method in SQL Server is:

where shipdate >= datefromparts(year(getdate()) - 3, 1, 1)

For any date in 2021, this returns all rows from 2021, 2020, 2019, and 2018. If you don't want 2018, then use - 2 rather than - 3.

This formulation has the option of being optimizer friendly (allowing the use of indexes and partitions, for example). Two other common says to write this are not optimizer friendly:

where datediff(year, shipdate, getdate()) <= 3
where year(shipdate) >= year(getdate()) - 3

(Once again, the "3" might be "2" depending on what you really intend.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786