AS you've found out, the use of ISDATE is unreliable when doing implicit conversions.
Try these:
SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT ISDATE('2013-01-08 15:44:12.2081606') AS test
SELECT ISDATE('2013-01-08 15:44:12.208') AS test
The different results you get when trying to test a date's validity depends on the datatype.
Try these and see what happens:
SELECT CONVERT(SMALLDATETIME,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIME,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIME2,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIMEOFFSET,'2013-01-08 15:44:12.2081606 +05:30') AS test
Here are two procedures that will test for valid dates. You can modify these to test for various datatypes as you are trying to do. These procedures avoid using the ISDATE function and simply attempt to convert what may or may not be a valid date into a valid datetime datatype. The first procedure tests based on the DATETIMEOFFSET datatype which is very forgiving and will return as a valid date something like '1234' as '1234-01-01 00:00:00.0000000 +00:00' which is in fact a valid date.
The second procedure tests based on the less forgiving SMALLDATETIME datatype, but also calls the first procedure if you want to check a date both ways. Of course, this second procedure can be modified to skip the call to the external procedure and the datatypes can be changed as well. But doing so may give different results!
I've also included a parameter in the second procedure so that the DATEFORMAT can be specified with a default of 'DMY'.
CREATE PROCEDURE [dbo].[IsValidDate]
@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'DMY' -- MDY, DMY, YMD, YDM, MYD, DYM
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT @sDateFormat
DECLARE
@dStdDate SMALLDATETIME
,@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@bIsValidDate BIT
SET @dStdDate = NULL
SET @dUMCDate = NULL
/* This section is optional and could be left out */
/* 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
/* End of optional section */
/* 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
GO
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
GO