問題描述
我有這個查詢,我嘗試將其轉換為各種格式,我的意思是日期時間等,但它不起作用并引發錯誤:
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.
這篇關于從字符串轉換日期和/或時間失敗的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!