HelixTitan HelixTitan - 1 month ago 5
SQL Question

SQL Retrieve data from First day of the month a year ago to today

I understand that you can already get info from the last year using

DATEADD(year,-1,GETDATE())
but I want to search from the first day of that month a year ago. So, for example, today is 10/17/2016. I want to search from November 1st from 2015. How would I format that?

Answer
select * 
from
where datefield>=
dateadd(year,-1,
DATEADD(month, DATEDIFF(month, 0, getdate()), 0)--gives first date of month
)

or from 2012: you can use DATEFROMPARTS as mentioned by Alex in comments

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