我有一個帶有datedatatype 列的表varchar。值為'2022-03-08 07:00',2022-03-08 07:30 ... 。
在我的存盤程序中，我有一個型別的引數DATE和一個值'2022-3-8'
DECLARE @d DATE = '2022-3-8'
SELECT *, r.date AS date, @d AS d
FROM Readings AS r
WHERE CONVERT(VARCHAR, r.date, 23) = @d
我該如何比較這兩者？我收到此錯誤：
從字串轉換日期和/或時間時轉換失敗。
我想洗掉 time 組件并比較'2022-03-08'vs '2022-3-8'。注意月份和日期數字中的前導零。
uj5u.com熱心網友回復：
TRY_CAST或TRY_CONVERT將您的字串轉換為日期并在不可能的情況下回傳 null。
SELECT *, TRY_CAST(r.date AS DATE) as date, @d AS d
FROM Readings r
WHERE TRY_CAST(r.date AS DATE) = @d
例子：
DECLARE @d DATE = '2022-3-8';
WITH Readings AS
(
SELECT '2022-03-08 07:00' AS date
UNION ALL
SELECT '2022-03-08 07:30'
UNION ALL
SELECT '2022-03-06 17:30' --will be false
UNION ALL
SELECT '2022-02-31 07:30' --invalid string
)
SELECT r.date as OriginalString
, TRY_CAST(r.date AS DATE) as CastDate
, TRY_CONVERT(DATE,r.date,23) as ConvertDate
, @d AS d
, CASE WHEN TRY_CAST(r.date AS DATE) = @d THEN 1 ELSE 0 END AS Matched
FROM Readings r
uj5u.com熱心網友回復：
使用right(replicate('0',2) value,2)可以將1 個 one_digit 數字更改為two_digit 數字（1=>01）。使用PARSENAME進行拆分，使用concat連接字串
DECLARE @d DATE = '2022-3-8'
SELECT Concat(( Parsename(Replace(@d, '-', '.'), 3) )/*year*/, '-', RIGHT(
Replicate('0', 2) ( Parsename(Replace(@d, '-', '.'), 2) ), 2)
/*month*/,
'-', RIGHT(Replicate('0', 2) ( Parsename(Replace(@d, '-', '.'),
1) ), 2
)/*day*/) as d
或在您的查詢中
DECLARE @d DATE = '2022-3-8'
SELECT *,
r.date
AS date,
,@d
FROM readings AS r
WHERE CONVERT(VARCHAR, r.date, 23) =
Concat(( Parsename(Replace(@d, '-', '.'), 3) )/*year*/, '-', RIGHT(
Replicate('0', 2) ( Parsename(Replace(@d, '-', '.'), 2) ), 2)/*month*/,
'-',
RIGHT(Replicate('0', 2) ( Parsename(Replace(@d, '-', '.'), 1) ), 2)
/*day*/)
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/441130.html