If we have Finyear and month then we create a date for examples:
DECLARE @finyear CHAR(5) = '202425';
DECLARE @month smallint = 5;
--Get Year
DECLARE @year smallint = CAST(LEFT(@finyear, 4) AS INT) + (CASE WHEN @month < 4 THEN 1 ELSE 0 END)
--Create first date----
SELECT DATEFROMPARTS(@year,@month,1);
--output: 2024-05-01
Create function for that:
CREATE OR ALTER FUNCTION dbo.Fn_getFirstDateOfMonthInFinYear
(
@month INT,
@finyear CHAR(5)
)
RETURNS DATE
AS
BEGIN
DECLARE @year INT = CAST(LEFT(@finyear, 4) AS INT) + (CASE WHEN @month < 4 THEN 1 ELSE 0 END);
RETURN DATEFROMPARTS(@year, @month, 1);
END;
GO
DECLARE @finyear CHAR(5) = '202425';
DECLARE @month smallint = 5;
SELECT dbo.Fn_getFirstDateOfMonthInFinYear(@month, @finyear) AS FirstDateOfMonth;
--output: 2024-05-01