sql server语言 - ISDATE在TSQL中不同日期格式的函数

Question

我需要在SQL Server 2008的另一个应用程序中将VARCHAR值转换为DATETIME，以便在视图的多个列中进行排序和格式化(以区域设置格式显示)。

目前有两个问题。

1. VARCHAR值的输入格式不同(但一致)列级)
2. 也可能存在错误值(例如:20..05.2015)

TRY_CONVERT函数只适用于SQL Server 2012和更高版本。

ISDATE不工作，因为视图包含不同的日期格式，我既不能在用户定义函数中设置语言，也不能在视图中设置语言，这将导致ISDATE使用德国日期格式。对于我的问题有没有更简单的解决办法?我的第一个想法是写一个像

这样的函数

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

使用CONVERT函数的格式编号，但手动检查每种可能的格式让我有点害怕。

示例:TryConvertStringAsDatetime('20.05.2015'， 104)(带有一些伪代码)

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

这就是我现在想出来的函数:

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

我可能会这样写:

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  

注意:测试长度和使用patindex只能确保一般格式，因此您需要在try块中调用此函数，以防日期和月份颠倒，从而导致转换错误。
另一方面，向此函数添加支持的格式非常容易—您所要做的就是添加具有正确的patindex和长度以及正确的转换样式的when子句。

另一个选项是确保字符串实际上可以转换为日期。
这将使您的函数更复杂，因此更难编写，但将更容易使用，因为它将最小化引发转换错误的机会:

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

Answer 3

在SQLCLR中执行此操作，在速度和功能方面可能会有更好的运气(正如@Tab和@Zohar在各种评论中所指出的那样)。

。NET/c#代码:

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;
    }
}

测试:

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