I’m new to tSQL and trying to write a script that will return the first day of the quarter and the last day of the week i.e., 04/01-04/06, the first and last day of every week in the quarter e.g., 06/23-06-29, and the last week of the quarter e.g., 06/30 or 12/29-12/31.
CASE
WHEN GETDATE() BETWEEN FirstDateofQuarter AND LastDateofQuarter
THEN FORMAT(DATEPART(MONTH, dateadd(DAY,0,[FirstDateofWeek])), '00') + '/' + FORMAT(DATEPART(DAY, dateadd(DAY,0,[FirstDateofWeek])), '00') + '-' +
FORMAT(DATEPART(MONTH, dateadd(DAY,0,[FirstDateofWeek])), '00') + '/' + FORMAT(DATEPART(DAY, dateadd(DAY,0,[LastDateofWeek])), '00')
ELSE ''
END AS [Quarter_Date_Axis]
Our weeks start on Sundays, so I’m returning 03/31 for the start of the quarter and that’s obviously wrong, should be 04/01. I’m also not getting the month to rollover e.g., 05/26-05/01 should be 05/26-06/01, and finally the last week of the quarter is showing as 06/30-06/06 when it should be just 06/30. Any thoughts or tips would be helpful, thanks
wanderingpanda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.