try this way

SELECT CASE WHEN ISDATE(@string) = 1 
  AND @string LIKE '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]' 
  THEN 1 ELSE 0 END;

@string is date.

You dont store specific format (i think ms sql stores it as two integers), you select your format for output. And when I say you select, I mean you have your default (mostly set automatically when installing MS SQL or whatever you use based on your country, timezone, etc - you can change this) and those which you choose to when executing scripts.

First and foremost: If the date value is stored in date or datetime or equivalent temporal data type column there is no such thing as date format in the database level. The date itself was validated by the rules defined in the specific database engine's given data type and stored in whatever binary format it defines.

If the data is a STRING (from a file or a varchar column for example), then you can validate if it is in a given format using the TO_DATE() or TRY_CONVERT() functions in newer versions of SQL Server and STR_TO_DATE() in MySQL, or you can use 3rd party/self written modules/clrs to do it.

These validations will only check if the string maches the given format and the date parts are in the acceptable range of dates, but it will not test the meaning of the value. Converting the 02/03/2005 string to date is valid for the MM/DD/YYYY and DD/MM/YYYY format too, and there is no way to tell which one is the real value unless we have information about the environment originally stored it.

NEVER EVER store temporal data in character based column (like varchar), use the data type matches your needs (DATE/DATETIME/TIMESTAMP/whatever).

You can set the format but as far as I know you cant check the format.

try this to find format of values for date or varchar date field. Thanks

DECLARE  @Description VARCHAR(20)
SET @Description='200202'
SELECT CASE WHEN patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 101 
            WHEN patindex('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 110
            WHEN patindex('%[0-9][0-9][0-9][0-9].[0-9][0-9].[0-9][0-9]%',@Description)=1 THEN 102 
            WHEN patindex('%[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9]%',@Description)=1 THEN 111
            WHEN patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@Description)=1 THEN 112
            WHEN patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 103 
            WHEN patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 104
            WHEN patindex('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%',@Description)=1 THEN 105
    ELSE 101 END

This already has an accepted answer but I found a way to do this without regular expressions if anyone wants:

DECLARE @dateString VARCHAR(12) = '2023-05-12';

SELECT CASE WHEN TRY_CAST(@dateString AS DATE) IS NOT NULL AND @dateString = FORMAT(TRY_CAST(@dateString AS DATE), 'yyyy-MM-dd')
            THEN 'Valid'
            ELSE 'Invalid'
       END AS ValidationResult;

Hope it helps!