Get period start of month period (bimester, trimester, quarter, and semester)

I'm trying to get the period start of a given month period (2 for bimester, 3 for trimester, 4 for quarter, and 6 for semester).

Here's what I've tried:

DECLARE @param_MONTH_PERIOD TINYINT = 2;
DECLARE @local_MONTH        TINYINT = 12;
DECLARE @local_MONTH_DIFF   TINYINT = @local_MONTH - @param_MONTH_PERIOD
DECLARE @local_MONTH_START  TINYINT = (@local_MONTH_DIFF * 2) - @local_MONTH + @param_MONTH_PERIOD + 1;

The result should be 11, as the bimester starts in November, but the code crashes with other numbers.

For example, using October and semester as the month period value (6), it returns a value of 5 instead of 6, as the semester starts in June, not May.

Any help would be appreciated.

If you ultimately need to work with actual date or date/time values, it may be worth looking at the DATE_BUCKET() function (available in SQL Server 2022 and later). This might avoid steps that extract date components and later rebuild the period start dates from the calculated components.

SELECT DATE_BUCKET(month, @param_MONTH_PERIOD, @SomeDate) AS PeriodStart

By default, DATE_BUCKET() uses an origin date of 1900-01-01 to define date buckets of various sizes. This works for your scenario. However, if you were dealing with fiscal years or some other scenario where you needed some origin other than January, an explicit origin can be specified.

DECLARE @Jan1 DATE = '2000-01-01' -- Might be some month other than January
SELECT DATE_BUCKET(month, @param_MONTH_PERIOD, @SomeDate, @Jan1) AS PeriodStart

Note that both date parameters to the DATE_BUCKET() function must have the exact same type, such as DATE, DATETIME, or DATETIME2.

For versions prior to 2022, you can use a DATEDIFF()/DATEADD() combination

SELECT DATEADD(month, DATEDIFF(month, 0, @SomeDate) / @period * @period, 0) AS PeriodStart,

Here the 0 is the equivalent of 1900-01-01.

Example:

DECLARE @Jan1 DATE = '2000-01-01'  -- Any January 1 will do

SELECT
    D.SomeDate,
    DATE_BUCKET(month, 1, D.SomeDate, @Jan1 ) AS Month,
    DATE_BUCKET(month, 2, D.SomeDate, @Jan1 ) AS Bimester,
    DATE_BUCKET(month, 3, D.SomeDate, @Jan1 ) AS Quarter,
    DATE_BUCKET(month, 4, D.SomeDate, @Jan1 ) AS Trimester,
    DATE_BUCKET(month, 6, D.SomeDate, @Jan1 ) AS Semester
FROM GENERATE_SERIES(0, 500, 14) S  -- Sparse sample dates
CROSS APPLY(SELECT DATEADD(day, S.value, CAST('2025-01-01' AS DATE)) AS SomeDate) D
ORDER BY D.SomeDate

Results:

See this db<<>fiddle for a demo.