Here's two procedures I use for very thorough data validation. These procedures are much more reliable than ISDATE because ISDATE may return different results when converting from a string depending on the DATETIME datatype being converted to.
In these procedures I use SMALLDATETIME to validate non-UMC dates and DATETIMEOFFSET to validate UMC dates. If you know that you will always be using DATETIME or DATETIME2 (or even just TIME) then you will want to use those types in the TRY block.
Also note the DATEFORMAT parameter (default DMY) which allows validation even if the date format is different than the server default. The first procedure is all you need to run, but it calls the second procedure internally. Lots of examples below.
CREATE PROCEDURE [dbo].[IsValidDate]
@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'DMY' -- MDY, DMY, YMD, YDM, MYD, DYM
AS
BEGIN
/* This procedure requires procedure IsValidUMCDate */
SET NOCOUNT ON
SET DATEFORMAT @sDateFormat
DECLARE
@dStdDate SMALLDATETIME
,@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@bIsValidDate BIT
SET @dStdDate = NULL
SET @dUMCDate = NULL
/* Check to see if this is a valid UMC date */
IF OBJECT_ID('tempdb..#CheckUMCDate') IS NOT NULL
DROP TABLE #CheckUMCDate
CREATE TABLE #CheckUMCDate (
[ID] INT IDENTITY(1,1) NOT NULL,
[InputDate] VARCHAR(50) NULL,
[ConvertedDate] VARCHAR(50) NULL,
[IsValidUMCDate] BIT NULL
PRIMARY KEY (ID))
BEGIN TRY
INSERT INTO #CheckUMCDate
EXEC dbo.IsValidUMCDate @sDate
END TRY
BEGIN CATCH
INSERT INTO #CheckUMCDate
SELECT
@sDate AS InputDate
,NULL ConvertedDate
,0 AS IsValidUMCDate
END CATCH
SELECT
@dUMCDate = ConvertedDate
,@bIsValidUMCDate = IsValidUMCDate
FROM
#CheckUMCDate
/* Check using regular SMALLDATETIME datatype */
BEGIN TRY
SET @dStdDate = CONVERT(SMALLDATETIME,@sDate)
SET @bIsValidDate = 1
END TRY
BEGIN CATCH
SET @bIsValidDate = 0
SET @dStdDate = NULL
END CATCH
SELECT
@sDate AS InputDate
,@dStdDate AS StdDate
,@bIsValidDate AS IsValidDate
,@dUMCDate AS UMCDate
,@bIsValidUMCDate AS IsValidUMCDate
/*
EXAMPLES:
EXEC dbo.IsValidDate '01-07-2001'-- Valid date
EXEC dbo.IsValidDate '1/7/2001'-- Valid date
EXEC dbo.IsValidDate '07-01-2001'-- Valid date
EXEC dbo.IsValidDate '7/1/2001'-- Valid date
EXEC dbo.IsValidDate '29-12-2013','DMY'-- Valid date in DMY format
EXEC dbo.IsValidDate '29-12-2013','MDY'-- Invalid date in MDY format
EXEC dbo.IsValidDate '32-12-2013'-- ERROR: Date out of range
EXEC dbo.IsValidDate '29-02-2013'-- ERROR: Not a leap year
EXEC dbo.IsValidDate 'xyz'-- ERROR: Invalid date
EXEC dbo.IsValidDate '1234'-- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.000' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'-- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606'-- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'-- Invalid std date/valid umc date
*/
END
CREATE PROCEDURE [dbo].[IsValidUMCDate]
@sDate VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT DMY
DECLARE
@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@ERROR BIT
SET @dUMCDate = NULL
BEGIN TRY
SET @dUMCDate = CONVERT(DATETIMEOFFSET,@sDate)
SET @bIsValidUMCDate = 1
END TRY
BEGIN CATCH
SET @bIsValidUMCDate = 0
END CATCH
SET @ERROR = @bIsValidUMCDate
SELECT
@sDate AS InputDate
,@dUMCDate AS ConvertedDate
,@ERROR AS IsValidUMCDate
/*
EXAMPLES:
EXEC dbo.IsValidUMCDate '01-07-2001'-- Valid date
EXEC dbo.IsValidUMCDate '1/7/2001'-- Valid date
EXEC dbo.IsValidUMCDate '07-01-2001'-- Valid date
EXEC dbo.IsValidUMCDate '7/1/2001-- Valid date
EXEC dbo.IsValidUMCDate '29-12-2013'-- Valid date
EXEC dbo.IsValidUMCDate '32-12-2013'-- ERROR: Date out of range
EXEC dbo.IsValidUMCDate '29-02-2013'-- ERROR: Not a leap year
EXEC dbo.IsValidUMCDate 'xyz'-- ERROR: Invalid date
EXEC dbo.IsValidUMCDate '1234'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.000'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.208'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606 +05:30'-- Valid date
*/
END