Use the ISDATE() function
The ISDATE() function determines whether the variable or the expression contains a valid date. It returns 1(true) if the input expression is a valid date;
otherwise, it returns 0 (false).
For eg:
DECLARE @dt varchar(10)
SET @dt = '02/21/08'
SELECT ISDATE(@dt)-- Returns 1
DECLARE @dt varchar(10)
SET @dt = '13/21/08'
SELECT ISDATE(@dt)-- Returns 0 as 13 is not a valid month
