I have a stored procedure that runs once a month for the whole previous year. So for example 5-1-18 to 5-1-19. I am going to create a job that fires it off at the first of each month. I need my stored procedure to be able to revert back to the first of the month when debugging. So if it’s the 21st when ran, I need the stored procedure to start from the 1st.
When 'Pre12Months' Then DATEADD(year,-1, EoMonth @Date,-1))
The fix should start from the beginning of the month even if ran in the middle of the month.
Advertisement
Answer
I would use the DATEFROMPARTS function, assuming you’re SQL 2012 or newer:
DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, GETDATE())), MONTH(GETDATE()), 1)
will get you last year’s first of the same month, for example. No need to figure out how to get the first of a month when you can just say “give me the first”.