Check if format is YYYY-MM-DD

I need to check if the format stored in an SQL table is YYYY-MM-DD.

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.

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.

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).