久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

從字符串轉換日期和/或時間失敗

Converting date and/or time from character string is failing(從字符串轉換日期和/或時間失敗)
本文介紹了從字符串轉換日期和/或時間失敗的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!

問題描述

我有這個查詢,我嘗試將其轉換為各種格式,我的意思是日期時間等,但它不起作用并引發錯誤:

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的數據類型為DateOpeningTime的數據類型為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模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個表創建視圖?)
Create calculated value based on calculated value inside previous row(根據前一行內的計算值創建計算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板: 久久精品久久久久久 | 国产91久久精品一区二区 | 在线看黄免费 | 天天精品在线 | 秋霞av国产精品一区 | 国产伦精品一区二区三区精品视频 | 亚洲天堂色 | 成人做爰www免费看 午夜精品久久久久久久久久久久 | 97色在线视频 | 国产精品欧美一区二区三区不卡 | 色毛片 | 中文字幕av一区 | 日韩在线观看中文字幕 | 久久综合狠狠综合久久综合88 | 99精品久久久 | 福利视频一区二区 | 黄片毛片在线观看 | 欧美精品在线一区二区三区 | 欧美精品日韩精品国产精品 | 日本电影免费完整观看 | 亚洲高清视频在线 | 国产精品成人一区二区三区 | 亚洲精品9999| 国产精品欧美一区二区 | 一区二区三区四区不卡 | 欧美三级免费观看 | 91国内精精品久久久久久婷婷 | 国产精品一区免费 | 久久久久国产一区二区三区 | 国产精品一区视频 | 成人综合视频在线 | 91精品一区 | 精品欧美一区二区三区精品久久 | 九九热在线视频免费观看 | 超碰欧美 | 激情国产 | 国产亚洲精品精品国产亚洲综合 | 亚洲欧美一区二区三区在线 | 亚洲欧洲在线看 | 99久久婷婷国产综合精品电影 | 精品九九九 |