Finding a date range for quarter based on yyyymm input

Transact-SQL
1

Hi. I need to get a quarter start date in YYYYMM format based on a YYYYMM input. For example -

Within the line of code if I have, say, 201609 hardcoded somewhere (this will be defined eventually with a substitution variable), then the output will be 201607 (July being the start of that particular quarter).

I also need the same for the quarter end date i.e. the output will be 201609.

If the input is 201610 then the output for start will be 201610 and the end will be 201612. (Separate lines of code required for start and end)

Thanks!

2

You can convert the number to a date, and use the DATEPART function to find the beginning of quarter .

Alternatively, you can use arithmetic to compute what you need, for example like this (not very well, tested, but I think it should work, or at the very least, give you something to start with)

declare @yyyyymm int = 201609;
select
	@yyyyymm/100*100 + (@yyyyymm%100-1)/3*3+1,
	@yyyyymm/100*100 + (@yyyyymm%100-1)/3*3+3
1 Like
3

Alternative:

select (@yyyymm-1)/3*3+1
      ,(@yyyymm-1)/3*3+3
1 Like
4

Since it's a date, I'd treat it as a date:

DECLARE @yyyyymm int;
SET @yyyyymm = 201609;
SELECT @yyyyymm = CONVERT(varchar(6), DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CAST(@yyyyymm AS varchar(6)) + '01'), 0), 112)
SELECT @yyyyymm