CODEWITHSUNDEEP
sqlsql-serverdategetdate
jwalls91
jwalls91

Reputation: 351

SELECT previous year up to current date

I have a table which has a Start Date column for example:

Start Date
2015/01/05
2015/02/08
2016/01/10
2017/02/10
etc...

I am trying to put into my WHERE clause to select all records where it is one year prior based on the current GETDATE().

For example, if today is July 2019 and I run the query, I'd like for it to run and give me Start Dates starting from July of 2018 up until June of 2019. And if I run it for August of 2019, I'd like for it to show Start Dates from August of 2018 up until July of 2019, and so on. Basically up until the month before of the current date.

Currently I have this in my WHERE clause:

WHERE start_date between DATEADD(YEAR,-1, GETDATE()) and GETDATE()

but this appears I believe to get just one year prior up until the current date exact.

Is there a better way for me to do this?

Upvotes: 0

Views: 11672

Answers (2)

forpas
forpas

Reputation: 164069

I think that this is your requirement:

WHERE start_date BETWEEN
  DATEFROMPARTS(YEAR(GETDATE()) - 1, MONTH(GETDATE()), 1) 
  AND
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)

With:

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

you get the 1st day of current month in last year.
With:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)

you get the last Day of previous month.
See the demo.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can use datefromparts() in SQL Server:

where start_date >= datefromparts(year(getdate()) - 1, 1, 1) and
      start_date < datefromparts(year(getdate() - 1, month(getdate()), day(getdate())

Upvotes: 1

Related Questions