Get previous year til may

Asked by At

I want all values from last year til 2018-05-13. I want to run the query everyday so it should know that tomorrow it should get all values from last year til 2018-05-14 without specifying the date.

I use this code to get all values for this year and would like to have something similar for retrieving values from last year:

YEAR(Created) = YEAR(GETDATE())

3 Answers

2
dvo On Best Solutions

You can use DATEADD and DATEDIFF to manipulate the date based on GETDATE() to get to where you want. You can go one step further and do DATEADD(ms,-3,DATEADD(DAY,1,DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()), 0))) to get the end of the day for the date one year ago.

SELECT * FROM Table WHERE Date 
BETWEEN DATEADD(YEAR, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) 
AND DATEADD(YEAR, -1, GETDATE())
1
JohnHC On

Assuming SQL Server:

select *
from MyTable t1
where SomeDateColumn between dateadd(year,-1,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) 
    and getdate()

This gets all of last year, and this year to date

If you just want from today last year to today:

select *
from MyTable t1
where SomeDateColumn between dateadd(year,-1,GETDATE()) 
    and getdate()
0
Gordon Linoff On

I think you want:

where datecol >= dateadd(year, -1, convert(date, getdate())) and
      datecol < convert(date, getdate())

This gets the last complete year, for both date and date/time columns. If you want to include today, then:

where datecol > dateadd(year, -1, convert(date, getdate())) and
      datecol <= convert(date, getdate())