Setting SQL variables for First day of month and 1 year before

Possibly a dumb question because I’m totally new to SQL (I mean never touched it before today ????) but I’m adjusting a report in a program we use, so it always gets values for the year ending last month (we run it at the beginning of every month). So, I want @EndDate to be either 12:59pm of the end of last month, or 00:00am of first of this month, and I want @BeginDate to be same minus 1 year. My code is getting a ‘syntax error near keyword SET’ – I barely even know what this means, and the past hour on google hasn’t helped.

Code:

DECLARE @BeginDate DateTime;
DECLARE @EndDate DateTime;
SET @EndDate = CONVERT(Datetime, CONVERT(date, DATEFROMPARTS(YEAR(GetDate()), MONTH(GetDate()),1)), -- Beginning of this month at 00:00 AM
SET @BeginDate = DATEADD(yy, -1, @EndDate) -- a year before the End date

This is all I’ve added to the existing report, so if it doesn’t work completely independently it won’t work at all. Over to you guys … any suggestions? I’m guessing it’s a simple, dumb mistake?

Answer

You could try formating it to datetime

DECLARE @EndDate DateTime, @BeginDate DateTime;
SET @EndDate = FORMAT(GetDate(), 'yyyy-MM-01 00:00:00.000')
SET @BeginDate = DATEADD(yy, -1, @EndDate)

Advertisement

Answer