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

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

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