ISDATE Function for different date formats in TSQL

Question

I need to convert VARCHAR values into DATETIME in multiple columns of a view for sorting and formatting (displaying in locale format) purposes in another application on SQL Server 2008.

There are currently two problems.

1. The input format of the VARCHAR values differ (but consistent at column level)
2. Also there may be faulty values (e.g.: 20..05.2015)

Unfortunately the TRY_CONVERT function is available just for SQL Server 2012 and later.

ISDATE does not work because the view contains different date formats and I can neither set the language inside user defined functions nor in views, which would cause ISDATE to work with german date formats for example.

Is there any easier solution for my problem? My first thought was to write a function like

FUNCTION TryConvertStringAsDatetime (   @value VARCHAR(MAX),
                                        @format INT
                                    )

that uses the format numbers of the CONVERT function, but checking for every possible format manually scares me a bit.

Example: TryConvertStringAsDatetime('20.05.2015', 104) (with some pseudocode)

SET @day =  character 1 and 2 
SET @month = character 4 and 5
SET @year = character 7, 8, 9 and 10
SET @dateODBCFormat = @year - @month - @day (concatenated with hyphen and not subtracted :)
IF ISDATE(@dateODBCFormat ) = 1
    RETURN CONVERT(DATETIME, @dateODBCFormat, 120)
ELSE
    RETURN CONVERT(DATETIME, 0) (does the job)

Answer 1

This is the function I now came up with:

CREATE
FUNCTION TryConvertStringAsDatetime (   @value VARCHAR(MAX),
                                        @format INT
                                    )
RETURNS DATETIME
AS
/*
Tries to convert a given VARCHAR value to DATETIME.
Returns NULL if no value was specified or the value is not in the correct format.
*/
BEGIN

    DECLARE @length INT = LEN(@value)

    IF @length IS NULL OR @length < 10 OR @length > 23
        RETURN NULL

    DECLARE @day VARCHAR(2),
            @month VARCHAR(2),
            @year VARCHAR(4),
            @time VARCHAR(9)

    IF @format = 104 --dd.mm.yyyy hh:mi:ss(24h)
    BEGIN
        SET @day = SUBSTRING(@value, 1, 2)
        SET @month = SUBSTRING(@value, 4, 2)
        SET @year = SUBSTRING(@value, 7, 4) 
    END
    ELSE IF @format IN (120, 121) --yyyy-mm-dd hh:mi:ss(24h)
    BEGIN
        SET @year = SUBSTRING(@value, 1, 4)
        SET @month = SUBSTRING(@value, 6, 2)
        SET @day = SUBSTRING(@value, 9, 2)              
    END
    ELSE
        RETURN NULL -- currently only german and ODBC supported

    IF @length > 11
        SET @time = SUBSTRING(@value, 12, @length - 11)

    SET @value = @year + '-' + @month + '-' + @day + ISNULL(' ' + @time, '')

    IF ISDATE(@value) = 1
        RETURN CONVERT(DATETIME, @value, 121)

    RETURN NULL

END

Answer 2

You might have better luck in terms of both speed and functionality doing this in SQLCLR (as noted by @Tab and @Zohar in various comments).

.NET / C# code:

using System;
using System.Data.SqlTypes;
using System.Globalization;
using Microsoft.SqlServer.Server;

public class TryConvertStuff
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlDateTime TryConvertDateTime([SqlFacet(MaxSize = 50)] SqlString
        StringDate, [SqlFacet(MaxSize = 10)] SqlString Culture)
    {
        CultureInfo _Culture = CultureInfo.CurrentCulture;
        if (!Culture.IsNull && Culture.Value.Trim() != String.Empty)
        {
            _Culture = CultureInfo.GetCultureInfo(Culture.Value);
        }

        DateTime _RealDate;
        if (DateTime.TryParse(StringDate.Value, _Culture,
                               DateTimeStyles.None, out _RealDate))
        {
            return _RealDate;
        };

        return SqlDateTime.Null;
    }
}

Tests:

SELECT dbo.TryConvertDateTime(N'2019-04-20', N'en');  --  2019-04-20 00:00:00.000
SELECT dbo.TryConvertDateTime(N'2019-04-20f', N'en'); --  NULL
SELECT dbo.TryConvertDateTime(N'2019.04.20', N'en');  --  2019-04-20 00:00:00.000

SELECT dbo.TryConvertDateTime(N'20.04.2019', N'en');  --  NULL
SELECT dbo.TryConvertDateTime(N'20.04.2019', N'de');  --  2019-04-20 00:00:00.000
SELECT dbo.TryConvertDateTime(N'20.04.2019', NULL);   --  NULL

Answer 3

I would probably go with something like this:

CREATE FUNCTION TryConvertToDate 
(
    @InputString varchar(20)  
)
RETURNS Datetime
BEGIN
    DECLARE @DateTime datetime = NULL
    SET @DateTime = 
       CASE 
           WHEN LEN(@InputString) = 10 AND PATINDEX('[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]', @InputString)=1 THEN
               CONVERT(DateTime, @InputString, 104) -- German
           WHEN LEN(@InputString) = 10 AND PATINDEX('[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]', @InputString)=1 THEN
               CONVERT(DateTime, @InputString, 120) -- ODBC
           ELSE
               NULL -- unsuported format
       END
   RETURN @DateTime        
END  

Note: Testing for length and using patindex ensures only general format, so you need the call this function inside a try block in case the days and months are inverted and will cause a conversion error.
On the other hand, adding supported formats to this function is very easy - all you have to do is add a when clause with the correct patindex and length and the correct convert style.

Another option is to ensure the string can actually be converted to date.
This will make your function more complicated and thus harder to write, but will be easier to work with as it will reduce to minimum the chance of raising a conversion error:

CREATE FUNCTION TryConvertToDate 
(
    @InputString varchar(20)  
)
RETURNS Datetime
BEGIN


    DECLARE @DateValue date, @Days int, @Months int, @Years int

    IF LEN(@DateString) = 10 AND PATINDEX('[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]', @InputString)=1 -- German format
        BEGIN
            SELECT @Days = CAST(LEFT(@InputString, 2) As int),
                   @Months = CAST(SUBSTRING(@InputString, 4, 2) as int),
                   @Years = CAST(RIGHT(@InputString, 4) as int)
            -- NOTE: you will need to add a condition for leap years 
            IF (@Days < 31 AND @Months IN(4,6,9,12)) OR (@Days < 30 AND @Months = 2) 
               SET @DateValue = convert(date, @InputString, 104)
        END

    IF LEN(@InputString) = 10 AND PATINDEX('[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]', @InputString)=1 -- ODBC format
        BEGIN
            SELECT @Days = CAST(RIGHT(@InputString, 2) As int),
                   @Months = CAST(SUBSTRING(@InputString, 6, 2) as int),
                   @Years = CAST(LEFT(@InputString, 4) as int)
            -- NOTE: you will need to add a condition for leap years 
            IF (@Days < 31 AND @Months IN(4,6,9,12)) OR (@Days < 30 AND @Months = 2)
               SET @DateValue = convert(date, @InputString, 120)
        END

    RETURN @DateValue

END