SQL Server date format MM/DD/YYYY

Question

How do I check if a date string is in the MM/DD/YYYY format in SQL Server?

Answer 1

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

If the result is 1, it's a valid date, but there's no guarantee that it's the date the user meant. If they enter:

06/07/2012

There is no way to know if they meant June 7 or July 6. Your best bet is to make users pick dates from drop-downs or calendar controls, which allows you to control the format and avoid any needless interpretation. Your application layer can use strongly typed variables / parameters and insert into properly typed columns.