Back to all posts

How to get first date From month and Finyear

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…

If we have Finyear and month then we create a date for examples:

SQL
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:

SQL
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

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.