SQL statement to filter by last 5 years-CodePudding

I'm currently working on a SQL statement that filters the last 5 years of data I've added the below where clause which would take me to exactly 5 years from today.

I wanted to ask how I would improve this statement to contain all data in the 5th year as well.

e.g. The data would not be from the date 16/11/2016 but from 01/01/2016 if it was run today.

Where statement:

WHERE CreatedDate > DATEADD(YEAR,-5,GETDATE())

CodePudding user response：

DATEFROMPARTS is your friend:

WHERE CreatedDate >= DATEFROMPARTS(YEAR(GETDATE())-5,1,1)

CodePudding user response：

You could use

DATEFROMPARTS(YEAR(GETDATE()) - 5, 1, 1)

CodePudding user response：

you probably have a year function you can directly use :

where year(CreatedDate) >= year(GETDATE()) - 5