Validating Date in SQL Server

Hello everyone,

In this article, I will try to give information about validating dates in SQL Server.

In SQL Server, in some cases, you may want to know that the entered date is a valid date. Namely; The date 31092022 (a date expression without a period in between) normally does not exist. October is a month lasting 30 days.

You can easily do this using the code below.

DECLARE @input_str VARCHAR(25);
DECLARE @output_dt DATE;
SET @input_str = '31092022';

IF LEN(@input_str) >= 8
BEGIN
    DECLARE @date_str VARCHAR(25);
    SELECT @date_str
        = SUBSTRING(@input_str, 1, 2) + '.' + SUBSTRING(@input_str, 3, 2) + '.' + SUBSTRING(@input_str, 5, 4);

    BEGIN TRY
        SELECT @output_dt = CONVERT(DATE, @date_str, 104);
    END TRY
    BEGIN CATCH
    END CATCH;
END;

SELECT @output_dt;

When you run the above code, you will see a result similar to the one below.

As you can see, the date has been confirmed.

Good luck to everyone in business and life.