CODEWITHSUNDEEP
sqlsql-serverfunctiondatetimestored-procedures
King Love
King Love

Reputation: 7

GetDate Function

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.

Upvotes: 0

Views: 78

Answers (1)

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

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".

Upvotes: 1

Related Questions