Getting the first day of the next six months – using DATEFROMPARTS

It is always tempting just to MONTH(GETDATE())+1 – but then there’s the end of the year to consider.

DECLARE @ThisMonth DATE = (SELECT DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1));

DECLARE @NextMonth DATE = (SELECT
CASE
WHEN MONTH(@ThisMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@ThisMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@ThisMonth),MONTH(@ThisMonth)+1,1))
END);

DECLARE @NextMonth1 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth),MONTH(@NextMonth)+1,1))
END);

DECLARE @NextMonth2 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth1) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth1)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth1),MONTH(@NextMonth1)+1,1))
END);

DECLARE @NextMonth3 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth2) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth2)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth2),MONTH(@NextMonth2)+1,1))
END);

DECLARE @NextMonth4 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth3) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth3)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth3),MONTH(@NextMonth3)+1,1))
END);

SELECT @ThisMonth
UNION
SELECT @NextMonth
UNION
SELECT @NextMonth1
UNION
SELECT @NextMonth2
UNION
SELECT @NextMonth3
UNION
SELECT @NextMonth4