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

2021-09-10 00:00:00 sql tsql sql-server

我有这个查询，我尝试将其转换为各种格式，我的意思是日期时间等，但它不起作用并引发错误:

I have this query and I tried converting it to every format, I mean the date time etc but it doesn't work and throws error:

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

SELECT  W.Organization_ID, 
        W.NIT_No, 
        W.SchemeID,
        OpeningDate,
        OpeningTime,
        GETDATE(), 
        WorkNo, 
        CONVERT(decimal(10, 2), W.Cost) AS Cost, 
        WorkName, 
        W.ExpiryDate as ExpiryDate,
        CONVERT(VARCHAR,OpeningDate,106),
        CASE WHEN 
               CONVERT(DATETIME, CONVERT(VARCHAR(20),OpeningDate,106) + ' ' 
               + CONVERT(VARCHAR(20),OpeningTime,108))< GETDATE() 
             THEN 1 
             ELSE 0 END AS OpeningVaild
FROM Works W

CASE 部分抛出错误.

OpeningDate 是 Varchar 类型，OpeningTime 是 Time 类型.

OpeningDate is of type Varchar and OpeningTime is of type Time.

为什么?

推荐答案

所以我明白问题出在这部分:

So I understand the problem is with this part:

CASE WHEN CONVERT(DATETIME, CONVERT(VARCHAR(20),OpeningDate,106) + ' ' + CONVERT(VARCHAR(20),OpeningTime,108))

更新

自从我第一次发布我的答案以来，事实证明您将开放日期存储为 varchar 而不是 date.
首先，你应该停止这样做.永远不要将日期存储在 Date 列以外的任何地方(除非您也需要它们和时间，然后使用 DateTime2).有关更多信息，请阅读 Aaron Bertrand 的 要改掉的坏习惯:选择错误的数据类型.

Update

Since I've first posted my answer it turns out that you store the opening date as varchar instead of date.
First, you should stop doing that. Never store dates in anything other than a Date column (unless you need them with time as well, and then use DateTime2). For more information, read Aaron Bertrand's Bad habits to kick : choosing the wrong data type.

假设列的数据类型不能改变，你在问题的评论中写道:

Assuming the data type of the column can't change, you wrote in the comments to the question:

@ZoharPeled:这是opendate 2017-04-10的格式

@ZoharPeled: this is the format of openingdate 2017-04-10

说明将日期存储为字符串引起的问题之一 - 我或其他任何人如何知道那是 4 月 10 日还是 10 月 4 日?答案是我们不能.

Illustrating one of the problems caused by storing dates as strings - How can I, or anyone else for that matter, know if that's the 10th of April or the 4th of October? The answer is we can't.

因此，假设现在是 4 月 10 日，您可以使用 convert 将 126 作为样式参数将其转换为 DateTime:

So, assuming it's the 10th of April, you can convert it to DateTime using convert with 126 as the style parameter:

CASE 
    WHEN CONVERT(DateTime, OpeningDate, 126) + CAST(OpeningTime As DateTime) < GETDATE() THEN 
        1 
    ELSE 
        0 
END As OpeningVaild

第一个版本:

假设OpeningDate的数据类型为Date，OpeningTime的数据类型为Time，似乎就像您试图弄清楚这些列组合成 DateTime 是否在当前 DateTime 之前.

First version:

Assuming that the data type of OpeningDate is Date and the data type of OpeningTime is Time, Seems like you are attempting to figure out if these columns combination into a DateTime is before the current DateTime.

不是将它们转换为字符串并返回到 DateTime，您可以将它们都转换为 DateTime 并将它们简单地加在一起:

Instead of converting them into strings and back to DateTime, you can cast both to DateTime and simply add them together:

CASE 
    WHEN CAST(OpeningDate As DateTime) + CAST(OpeningTime As DateTime) < GETDATE() THEN 
        1 
    ELSE 
        0 
END As OpeningVaild

另一种选择是使用 GETDATE() 两次.我认为在 select 子句中应该无关紧要，但是在 where 子句中使用此选项很重要，因为第一个将使这些列不可搜索，这意味着数据库引擎将无法使用任何可能有助于语句执行计划的索引:

Another option would be to use GETDATE() twice. I don't think it should matter in the select clause, but in the where clause it's important to use this option since the first one will make these columns non-seargable, meaning the database engine will not be able to use any indexes that might help the execution plan of the statement:

CASE 
WHEN OpeningDate < CAST(GETDATE() AS DATE) 
     OR 
     (
         OpeningDate = CAST(GETDATE() AS DATE) 
         AND OpeningTime <= CAST(GETDATE() AS TIME)
     ) THEN 
    1 
ELSE 
    0 
END AS OpeningVaild

话虽如此，您的查询也有 CONVERT(VARCHAR,OpeningDate,106) - 106 样式返回日期的字符串表示为 dd mon yyyy - 含义11 个字符 - 因此将其更改为 CONVERT(CHAR(11),OpeningDate,106) 请注意，使用 varchar 而不指定长度默认为 30，这在这种情况下，因为它超过了你需要的 11 个字符，但是 不指定长度是一个坏习惯，你应该踢它.

That being said, your query also have CONVERT(VARCHAR,OpeningDate,106) - The 106 style returns a string representation of the date as dd mon yyyy - meaning 11 chars - so change that to CONVERT(CHAR(11),OpeningDate,106) Note that using varchar without specifying the length defaults to 30, which is not a problem in this case since it's more than he 11 chars you need, but it's a bad habit to not specify length and you should kick it.

相关文章