SQL 将字符串(日期或文本)转换为日期 [英] SQL convert string(date or text) to date

问题描述

我有一个数据库，其中有一个名为 stringNextDue 的列，其中包含日期(英国格式)和文本(例如过期"、已完成")等数据

I have a database which has a column called stringNextDue that contains data like dates (UK format) and text (e.g "overdue", "completed")

我正在尝试创建一个视图，显示从现在起一个月内到期的课程:

I am trying to create a view that shows courses that are due within a month from now:

WHERE 
    CONVERT(DATETIME, mt.stringNextDue , 103) < DATEADD(MONTH, 1, GETDATE())

这会引发错误:

从字符串转换日期和/或时间时转换失败.

Conversion failed when converting date and/or time from character string.

这可能是因为 stringNextDue 可能包含实际的文本字符串.

Which is probably due to the fact that stringNextDue may contain actual strings of text.

我尝试使用

WHERE 
    ISDATE(mt.NextDateString) = 1 
    AND CONVERT(DATETIME, mt.stringNextDue , 103) < DATEADD(MONTH, 1, GETDATE())

但是 ISDATE 只接受美国日期格式，因此忽略了许多实际日期作为字符串

But ISDATE only accepts US date formats therefore ignoring a lot of actual dates as strings

尝试了 set dateformat 'dmy'，它解决了 IsDate 问题，但无法在视图中使用.

Tried set dateformat 'dmy', which fixed the IsDate issue, but it cannot be used in views.

有什么建议吗?

服务器更新不是一种选择

Server update is not an option

推荐答案

如果你不能使用新的 TRY_CONVERT 你可以使用这样的函数:

If you cannot use the new TRY_CONVERT you might use a function like this:

注意:这不会像 31.06.2016 这样的错误日期，如果你需要这个，你必须修改 BETWEEN 1 AND 31...

Attention: This will not catch a wrong date like 31.06.2016, you'd have to modify the BETWEEN 1 AND 31 if you need this...

注意2:如果您的文本可能包含xml中禁止的字符，您应该将<替换为<，>替换为> 和 & 与 & ...

Attention2: If your text might include characters forbidden in xml you should replace < with <, > with > and & with & ...

CREATE FUNCTION dbo.TestDate(@TestString VARCHAR(100))
RETURNS DATE
AS
BEGIN
    DECLARE @x XML=CAST('<x>' + REPLACE(@TestString,'.','</x><x>') + '</x>' AS XML)

    DECLARE @p1 VARCHAR(10) = @x.value('x[1]','varchar(10)');
    DECLARE @p2 VARCHAR(10) = @x.value('x[2]','varchar(10)');
    DECLARE @p3 VARCHAR(10) = @x.value('x[3]','varchar(10)');

    IF    LEN(@p1)=2 AND ISNUMERIC(@p1)=1 AND CAST(@p1 AS INT) BETWEEN 1 AND 31
      AND LEN(@p2)=2 AND ISNUMERIC(@p2)=1 AND CAST(@p2 AS INT) BETWEEN 1 AND 12
      AND LEN(@p3)=4 AND ISNUMERIC(@p3)=1 AND CAST(@p3 AS INT) BETWEEN 1900 AND 2100

    RETURN CONVERT(DATETIME, @TestString , 103);

    RETURN NULL;
END
GO

SELECT
  dbo.TestDate('overdue') AS SureNoDate
 ,dbo.TestDate('01.04.2016') AS EuropeanDate
 ,dbo.TestDate('2016.04.01') AS WrongFormat
 ,dbo.TestDate('01.13.2016') AS BadDate;
GO

DROP FUNCTION dbo.TestDate;

结果

SureNoDate  EuropeanDate    WrongFormat BadDate
NULL        2016-04-01      NULL        NULL

您可能会回传一个有效日期 (RETURN GETDATE() ?) 而不是 RETURN NULL 用于外部比较.这取决于您的需求...

You might pass back a valid date (RETURN GETDATE() ?) instead of RETURN NULL for your comparisson outside. This depends on your needs...

这篇关于SQL 将字符串(日期或文本)转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！