更新

自從我第一次發(fā)布我的答案以來，事實(shí)證明您將開放日期存儲為 varchar 而不是 date.
首先，你應(yīng)該停止這樣做.永遠(yuǎn)不要將日期存儲在 Date 列以外的任何地方(除非您也需要它們和時(shí)間，然后使用 DateTime2).有關(guān)更多信息，請閱讀 Aaron Bertrand 的 要改掉的壞習(xí)慣:選擇錯(cuò)誤的數(shù)據(jù)類型.

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.

假設(shè)列的數(shù)據(jù)類型不能改變，你在問題的評論中寫道:

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.

因此，假設(shè)現(xiàn)在是 4 月 10 日，您可以使用 convert 將 126 作為樣式參數(shù)將其轉(zhuǎn)換為 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

第一個(gè)版本:

假設(shè)OpeningDate的數(shù)據(jù)類型為Date，OpeningTime的數(shù)據(jù)類型為Time，似乎就像您試圖弄清楚這些列組合成 DateTime 是否在當(dāng)前 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.

不是將它們轉(zhuǎn)換為字符串并返回到 DateTime，您可以將它們都轉(zhuǎn)換為 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() 兩次.我認(rèn)為在 select 子句中應(yīng)該無關(guān)緊要，但是在 where 子句中使用此選項(xiàng)很重要，因?yàn)榈谝粋€(gè)將使這些列不可搜索，這意味著數(shù)據(jù)庫引擎將無法使用任何可能有助于語句執(zhí)行計(jì)劃的索引:

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 個(gè)字符 - 因此將其更改為 CONVERT(CHAR(11),OpeningDate,106) 請注意，使用 varchar 而不指定長度默認(rèn)為 30，這在這種情況下，因?yàn)樗^了你需要的 11 個(gè)字符，但是 不指定長度是一個(gè)壞習(xí)慣，你應(yīng)該踢它.

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.

這篇關(guān)于從字符串轉(zhuǎn)換日期和/或時(shí)間失敗的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！