Sql-Server

將 varchar 數據轉換為日期時間失敗

  • January 10, 2018

我們正在將數據從遺留表（所有 varchar 欄位）移動到強類型對應提示歡呼

作為這項工作的一部分，如果所有數據都可以正確轉換為適當的類型，我們將從基本 Entity 表中獲取數據並將其轉儲到 Entity_New 中。否則，它將進入名為 Entity_Bad 的現有表的副本。

我們有一個規則引擎來驗證數據和類型，理論上，即使數據儲存在字元欄位中，數據也應該是乾淨的。現實是我在這裡發帖，因為有些東西已經關閉，我找不到它。Entity 中的 CompletionDate 欄位為 varchar(46) NULL

環境是

productversion  productlevel   edition
10.0.4064.0     SP2            Enterprise Edition (64-bit)

我的腳本展示了我在做什麼以及什麼是但不工作

SET NOCOUNT ON

DECLARE
   @startid int = 0
,   @stopid int = 796833


-------------------------------------------------------------------------------
-- Check doesn't find anything wrong with CompletionDate
-------------------------------------------------------------------------------
SELECT
   1
FROM
   [dbo].[Entity] E
   INNER JOIN
       dbo.EntityBatch_New PB
       ON E.FiscalYear = PB.FiscalYear
           AND E.HashCode = PB.HashCode
           AND E.AAKey = PB.AAKey
           AND PB.ProcessResultCode IN ('A','W','M')
WHERE
   PB.EntityBatchId BETWEEN @StartId AND @StopId
   AND
   (
       -- check
       (isDate(E.[CompletionDate]) = 0 AND E.[CompletionDate] IS NOT NULL)
       AND (isDate(E.[CompletionDate]) = 1 AND CAST(E.[CompletionDate] AS datetime) BETWEEN '1753-01-01T00:00:00.000' AND '9999-12-31T23:59:59.997')
   )

-------------------------------------------------------------------------------
-- Only row that shows as non-date is the NULL one, which is expected
-------------------------------------------------------------------------------
SELECT DISTINCT
   (E.[CompletionDate] )
,  isDate(E.[CompletionDate])
FROM
   [dbo].[Entity] E
   INNER JOIN
       dbo.EntityBatch_New PB
       ON E.FiscalYear = PB.FiscalYear
           AND E.HashCode = PB.HashCode
           AND PB.ProcessResultCode IN ('A','W','M')
   -- Ensure we aren't pulling something we have already processed
   LEFT OUTER JOIN
       [dbo].[Entity_new] N
       ON N.HashCode = E.HashCode
           AND N.FiscalYear = E.FiscalYear
               AND E.AAKey = N.AAKey
   -- Ensure we aren't pulling something we have already processed (or was bad)
   LEFT OUTER JOIN
       [dbo].[Entity_bad] BAD
       ON BAD.HashCode = E.HashCode
           AND BAD.FiscalYear = E.FiscalYear
               AND E.AAKey = BAD.AAKey
WHERE
   PB.EntityBatchId BETWEEN @StartId AND @StopId
   AND N.FiscalYear IS NULL
   AND BAD.FiscalYear IS NULL
ORDER BY 2

-------------------------------------------------------------------------------
-- Make the cast and it blows with
-- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
-------------------------------------------------------------------------------
SELECT DISTINCT
   (E.[CompletionDate] )
,   CAST(E.[CompletionDate] AS datetime) AS [CompletionDate]
FROM
   [dbo].[Entity] E
   INNER JOIN
       dbo.EntityBatch_New PB
       ON E.FiscalYear = PB.FiscalYear
           AND E.HashCode = PB.HashCode
           AND PB.ProcessResultCode IN ('A','W','M')
   -- Ensure we aren't pulling something we have already processed
   LEFT OUTER JOIN
       [dbo].[Entity_new] N
       ON N.HashCode = E.HashCode
           AND N.FiscalYear = E.FiscalYear
               AND E.AAKey = N.AAKey
   -- Ensure we aren't pulling something we have already processed (or was bad)
   LEFT OUTER JOIN
       [dbo].[Entity_bad] BAD
       ON BAD.HashCode = E.HashCode
           AND BAD.FiscalYear = E.FiscalYear
               AND E.AAKey = BAD.AAKey
WHERE
   PB.EntityBatchId BETWEEN @StartId AND @StopId
   AND N.FiscalYear IS NULL
   AND BAD.FiscalYear IS NULL


-------------------------------------------------------------------------------
-- Dump the values into a temporary table to slice and dice the values
-------------------------------------------------------------------------------
DECLARE @debug TABLE
(
   CompletionDate varchar(46) NULL
)
INSERT INTO
   @debug
SELECT DISTINCT
   (E.[CompletionDate] )
FROM
   [dbo].[Entity] E
   INNER JOIN
       dbo.EntityBatch_New PB
       ON E.FiscalYear = PB.FiscalYear
           AND E.HashCode = PB.HashCode
           AND PB.ProcessResultCode IN ('A','W','M')
   -- Ensure we aren't pulling something we have already processed
   LEFT OUTER JOIN
       [dbo].[Entity_new] N
       ON N.HashCode = E.HashCode
           AND N.FiscalYear = E.FiscalYear
               AND E.AAKey = N.AAKey
   -- Ensure we aren't pulling something we have already processed (or was bad)
   LEFT OUTER JOIN
       [dbo].[Entity_bad] BAD
       ON BAD.HashCode = E.HashCode
           AND BAD.FiscalYear = E.FiscalYear
               AND E.AAKey = BAD.AAKey
WHERE
   PB.EntityBatchId BETWEEN @StartId AND @StopId
   AND N.FiscalYear IS NULL
   AND BAD.FiscalYear IS NULL


-------------------------------------------------------------------------------
-- This is operating on all the same values as the failing query but magically works
-------------------------------------------------------------------------------
SELECT ALL
   CAST(E.[CompletionDate] AS datetime) AS [CompletionDate]
FROM
   @debug E



-------------------------------------------------------------------------------
-- Clearly, something is amiss when we extract the data so process each row
-- and find the culprit that way. Except this finds nothing wrong
-------------------------------------------------------------------------------
DECLARE @hash uniqueidentifier
,   @zee_date varchar(46)
,   @real_date datetime

DECLARE
   CSR CURSOR READ_ONLY
FOR
SELECT
   E.HashCode
,   E.[CompletionDate]
FROM
   [dbo].[Entity] E
   INNER JOIN
       dbo.EntityBatch_New PB
       ON E.FiscalYear = PB.FiscalYear
           AND E.HashCode = PB.HashCode
           AND PB.ProcessResultCode IN ('A','W','M')
   -- Ensure we aren't pulling something we have already processed
   LEFT OUTER JOIN
       [dbo].[Entity_new] N
       ON N.HashCode = E.HashCode
           AND N.FiscalYear = E.FiscalYear
               AND E.AAKey = N.AAKey
   -- Ensure we aren't pulling something we have already processed (or was bad)
   LEFT OUTER JOIN
       [dbo].[Entity_bad] BAD
       ON BAD.HashCode = E.HashCode
           AND BAD.FiscalYear = E.FiscalYear
               AND E.AAKey = BAD.AAKey
WHERE
   PB.EntityBatchId BETWEEN @StartId AND @StopId
   AND N.FiscalYear IS NULL
   AND BAD.FiscalYear IS NULL


OPEN CSR

FETCH NEXT FROM CSR INTO
   @hash, @zee_date

WHILE (@@fetch_status = 0)
BEGIN
   BEGIN TRY
       SELECT @real_date = cast(@zee_date AS datetime)
   END TRY
   BEGIN CATCH
       print 'In here'
       print @hash
       print @zee_date
       SELECT @hash, @zee_date
   END CATCH


   FETCH NEXT FROM CSR INTO
       @hash, @zee_date

END

CLOSE csr
DEALLOCATE csr

在用上面的程式碼牆批評你之後，這裡是上面查詢操作的 406 個唯一值。

DECLARE @REAL_DATES TABLE
(
   CompletionDate varchar(46) NULL
)

INSERT INTO
   @REAL_DATES 
SELECT
   NULL
UNION ALL SELECT '19000101'
UNION ALL SELECT '20100208'
UNION ALL SELECT '20100228'
UNION ALL SELECT '20100309'
UNION ALL SELECT '20100314'
UNION ALL SELECT '20100401'
UNION ALL SELECT '20100409'
UNION ALL SELECT '20100420'
UNION ALL SELECT '20100427'
UNION ALL SELECT '20100429'
UNION ALL SELECT '20100507'
UNION ALL SELECT '20100615'
UNION ALL SELECT '20100617'
UNION ALL SELECT '20100629'
UNION ALL SELECT '20100701'
UNION ALL SELECT '20100703'
UNION ALL SELECT '20100704'
UNION ALL SELECT '20100706'
UNION ALL SELECT '20100709'
UNION ALL SELECT '20100713'
UNION ALL SELECT '20100714'
UNION ALL SELECT '20100715'
UNION ALL SELECT '20100716'
UNION ALL SELECT '20100720'
UNION ALL SELECT '20100721'
UNION ALL SELECT '20100726'
UNION ALL SELECT '20100727'
UNION ALL SELECT '20100728'
UNION ALL SELECT '20100729'
UNION ALL SELECT '20100731'
UNION ALL SELECT '20100801'
UNION ALL SELECT '20100802'
UNION ALL SELECT '20100803'
UNION ALL SELECT '20100804'
UNION ALL SELECT '20100807'
UNION ALL SELECT '20100809'
UNION ALL SELECT '20100810'
UNION ALL SELECT '20100811'
UNION ALL SELECT '20100813'
UNION ALL SELECT '20100817'
UNION ALL SELECT '20100819'
UNION ALL SELECT '20100820'
UNION ALL SELECT '20100822'
UNION ALL SELECT '20100823'
UNION ALL SELECT '20100825'
UNION ALL SELECT '20100827'
UNION ALL SELECT '20100828'
UNION ALL SELECT '20100830'
UNION ALL SELECT '20100831'
UNION ALL SELECT '20100901'
UNION ALL SELECT '20100902'
UNION ALL SELECT '20100904'
UNION ALL SELECT '20100907'
UNION ALL SELECT '20100908'
UNION ALL SELECT '20100909'
UNION ALL SELECT '20100910'
UNION ALL SELECT '20100911'
UNION ALL SELECT '20100913'
UNION ALL SELECT '20100916'
UNION ALL SELECT '20100919'
UNION ALL SELECT '20100920'
UNION ALL SELECT '20100922'
UNION ALL SELECT '20100923'
UNION ALL SELECT '20100925'
UNION ALL SELECT '20100928'
UNION ALL SELECT '20101002'
UNION ALL SELECT '20101004'
UNION ALL SELECT '20101007'
UNION ALL SELECT '20101009'
UNION ALL SELECT '20101010'
UNION ALL SELECT '20101013'
UNION ALL SELECT '20101016'
UNION ALL SELECT '20101018'
UNION ALL SELECT '20101019'
UNION ALL SELECT '20101020'
UNION ALL SELECT '20101022'
UNION ALL SELECT '20101023'
UNION ALL SELECT '20101025'
UNION ALL SELECT '20101028'
UNION ALL SELECT '20101030'
UNION ALL SELECT '20101102'
UNION ALL SELECT '20101107'
UNION ALL SELECT '20101108'
UNION ALL SELECT '20101109'
UNION ALL SELECT '20101111'
UNION ALL SELECT '20101112'
UNION ALL SELECT '20101114'
UNION ALL SELECT '20101117'
UNION ALL SELECT '20101119'
UNION ALL SELECT '20101124'
UNION ALL SELECT '20101126'
UNION ALL SELECT '20101127'
UNION ALL SELECT '20101129'
UNION ALL SELECT '20101201'
UNION ALL SELECT '20101203'
UNION ALL SELECT '20101204'
UNION ALL SELECT '20101206'
UNION ALL SELECT '20101209'
UNION ALL SELECT '20101210'
UNION ALL SELECT '20101213'
UNION ALL SELECT '20101214'
UNION ALL SELECT '20101215'
UNION ALL SELECT '20101216'
UNION ALL SELECT '20101220'
UNION ALL SELECT '20101222'
UNION ALL SELECT '20101227'
UNION ALL SELECT '20101228'
UNION ALL SELECT '20101229'
UNION ALL SELECT '20101230'
UNION ALL SELECT '20110102'
UNION ALL SELECT '20110103'
UNION ALL SELECT '20110104'
UNION ALL SELECT '20110106'
UNION ALL SELECT '20110107'
UNION ALL SELECT '20110108'
UNION ALL SELECT '20110109'
UNION ALL SELECT '20110110'
UNION ALL SELECT '20110111'
UNION ALL SELECT '20110114'
UNION ALL SELECT '20110116'
UNION ALL SELECT '20110118'
UNION ALL SELECT '20110119'
UNION ALL SELECT '20110120'
UNION ALL SELECT '20110122'
UNION ALL SELECT '20110123'
UNION ALL SELECT '20110125'
UNION ALL SELECT '20110126'
UNION ALL SELECT '20110128'
UNION ALL SELECT '20110130'
UNION ALL SELECT '20110203'
UNION ALL SELECT '20110205'
UNION ALL SELECT '20110206'
UNION ALL SELECT '20110208'
UNION ALL SELECT '20110210'
UNION ALL SELECT '20110212'
UNION ALL SELECT '20110213'
UNION ALL SELECT '20110215'
UNION ALL SELECT '20110218'
UNION ALL SELECT '20110221'
UNION ALL SELECT '20110224'
UNION ALL SELECT '20110226'
UNION ALL SELECT '20110301'
UNION ALL SELECT '20110302'
UNION ALL SELECT '20110304'
UNION ALL SELECT '20110307'
UNION ALL SELECT '20110309'
UNION ALL SELECT '20110311'
UNION ALL SELECT '20110314'
UNION ALL SELECT '20110316'
UNION ALL SELECT '20110317'
UNION ALL SELECT '20110320'
UNION ALL SELECT '20110321'
UNION ALL SELECT '20110323'
UNION ALL SELECT '20110326'
UNION ALL SELECT '20110328'
UNION ALL SELECT '20110329'
UNION ALL SELECT '20110331'
UNION ALL SELECT '20110403'
UNION ALL SELECT '20110405'
UNION ALL SELECT '20110406'
UNION ALL SELECT '20110408'
UNION ALL SELECT '20110410'
UNION ALL SELECT '20110415'
UNION ALL SELECT '20110416'
UNION ALL SELECT '20110417'
UNION ALL SELECT '20110418'
UNION ALL SELECT '20110421'
UNION ALL SELECT '20110422'
UNION ALL SELECT '20110423'
UNION ALL SELECT '20110426'
UNION ALL SELECT '20110429'
UNION ALL SELECT '20110501'
UNION ALL SELECT '20110503'
UNION ALL SELECT '20110504'
UNION ALL SELECT '20110506'
UNION ALL SELECT '20110508'
UNION ALL SELECT '20110509'
UNION ALL SELECT '20110511'
UNION ALL SELECT '20110512'
UNION ALL SELECT '20110514'
UNION ALL SELECT '20110517'
UNION ALL SELECT '20110518'
UNION ALL SELECT '20110519'
UNION ALL SELECT '20110520'
UNION ALL SELECT '20110522'
UNION ALL SELECT '20110526'
UNION ALL SELECT '20110531'
UNION ALL SELECT '20110603'
UNION ALL SELECT '20110604'
UNION ALL SELECT '20110605'
UNION ALL SELECT '20110606'
UNION ALL SELECT '20110608'
UNION ALL SELECT '20110611'
UNION ALL SELECT '20110613'
UNION ALL SELECT '20110614'
UNION ALL SELECT '20110616'
UNION ALL SELECT '20110622'
UNION ALL SELECT '20110624'
UNION ALL SELECT '20110627'
UNION ALL SELECT '20110703'
UNION ALL SELECT '20110704'
UNION ALL SELECT '20110711'
UNION ALL SELECT '20110712'
UNION ALL SELECT '20110713'
UNION ALL SELECT '20110714'
UNION ALL SELECT '20110719'
UNION ALL SELECT '20110720'
UNION ALL SELECT '20110725'
UNION ALL SELECT '20110726'
UNION ALL SELECT '20110802'
UNION ALL SELECT '20110804'
UNION ALL SELECT '20110811'
UNION ALL SELECT '20090611'
UNION ALL SELECT '20091124'
UNION ALL SELECT '20100201'
UNION ALL SELECT '20100202'
UNION ALL SELECT '20100204'
UNION ALL SELECT '20100220'
UNION ALL SELECT '20100305'
UNION ALL SELECT '20100323'
UNION ALL SELECT '20100414'
UNION ALL SELECT '20100417'
UNION ALL SELECT '20100508'
UNION ALL SELECT '20100512'
UNION ALL SELECT '20100527'
UNION ALL SELECT '20100616'
UNION ALL SELECT '20100702'
UNION ALL SELECT '20100705'
UNION ALL SELECT '20100707'
UNION ALL SELECT '20100708'
UNION ALL SELECT '20100710'
UNION ALL SELECT '20100711'
UNION ALL SELECT '20100712'
UNION ALL SELECT '20100717'
UNION ALL SELECT '20100719'
UNION ALL SELECT '20100722'
UNION ALL SELECT '20100723'
UNION ALL SELECT '20100724'
UNION ALL SELECT '20100725'
UNION ALL SELECT '20100730'
UNION ALL SELECT '20100805'
UNION ALL SELECT '20100806'
UNION ALL SELECT '20100808'
UNION ALL SELECT '20100812'
UNION ALL SELECT '20100814'
UNION ALL SELECT '20100815'
UNION ALL SELECT '20100816'
UNION ALL SELECT '20100818'
UNION ALL SELECT '20100821'
UNION ALL SELECT '20100824'
UNION ALL SELECT '20100826'
UNION ALL SELECT '20100829'
UNION ALL SELECT '20100903'
UNION ALL SELECT '20100906'
UNION ALL SELECT '20100912'
UNION ALL SELECT '20100914'
UNION ALL SELECT '20100915'
UNION ALL SELECT '20100917'
UNION ALL SELECT '20100918'
UNION ALL SELECT '20100921'
UNION ALL SELECT '20100924'
UNION ALL SELECT '20100926'
UNION ALL SELECT '20100927'
UNION ALL SELECT '20100929'
UNION ALL SELECT '20100930'
UNION ALL SELECT '20101001'
UNION ALL SELECT '20101003'
UNION ALL SELECT '20101005'
UNION ALL SELECT '20101006'
UNION ALL SELECT '20101008'
UNION ALL SELECT '20101011'
UNION ALL SELECT '20101012'
UNION ALL SELECT '20101014'
UNION ALL SELECT '20101015'
UNION ALL SELECT '20101017'
UNION ALL SELECT '20101021'
UNION ALL SELECT '20101024'
UNION ALL SELECT '20101026'
UNION ALL SELECT '20101027'
UNION ALL SELECT '20101029'
UNION ALL SELECT '20101031'
UNION ALL SELECT '20101101'
UNION ALL SELECT '20101103'
UNION ALL SELECT '20101104'
UNION ALL SELECT '20101105'
UNION ALL SELECT '20101106'
UNION ALL SELECT '20101110'
UNION ALL SELECT '20101113'
UNION ALL SELECT '20101115'
UNION ALL SELECT '20101116'
UNION ALL SELECT '20101118'
UNION ALL SELECT '20101120'
UNION ALL SELECT '20101122'
UNION ALL SELECT '20101123'
UNION ALL SELECT '20101125'
UNION ALL SELECT '20101128'
UNION ALL SELECT '20101130'
UNION ALL SELECT '20101202'
UNION ALL SELECT '20101205'
UNION ALL SELECT '20101207'
UNION ALL SELECT '20101208'
UNION ALL SELECT '20101212'
UNION ALL SELECT '20101217'
UNION ALL SELECT '20101218'
UNION ALL SELECT '20101219'
UNION ALL SELECT '20101221'
UNION ALL SELECT '20101223'
UNION ALL SELECT '20101224'
UNION ALL SELECT '20101226'
UNION ALL SELECT '20101231'
UNION ALL SELECT '20110101'
UNION ALL SELECT '20110105'
UNION ALL SELECT '20110112'
UNION ALL SELECT '20110113'
UNION ALL SELECT '20110115'
UNION ALL SELECT '20110117'
UNION ALL SELECT '20110121'
UNION ALL SELECT '20110124'
UNION ALL SELECT '20110127'
UNION ALL SELECT '20110129'
UNION ALL SELECT '20110131'
UNION ALL SELECT '20110201'
UNION ALL SELECT '20110202'
UNION ALL SELECT '20110204'
UNION ALL SELECT '20110207'
UNION ALL SELECT '20110209'
UNION ALL SELECT '20110211'
UNION ALL SELECT '20110214'
UNION ALL SELECT '20110216'
UNION ALL SELECT '20110217'
UNION ALL SELECT '20110219'
UNION ALL SELECT '20110220'
UNION ALL SELECT '20110222'
UNION ALL SELECT '20110223'
UNION ALL SELECT '20110225'
UNION ALL SELECT '20110228'
UNION ALL SELECT '20110303'
UNION ALL SELECT '20110305'
UNION ALL SELECT '20110306'
UNION ALL SELECT '20110308'
UNION ALL SELECT '20110310'
UNION ALL SELECT '20110312'
UNION ALL SELECT '20110313'
UNION ALL SELECT '20110315'
UNION ALL SELECT '20110318'
UNION ALL SELECT '20110322'
UNION ALL SELECT '20110324'
UNION ALL SELECT '20110325'
UNION ALL SELECT '20110327'
UNION ALL SELECT '20110330'
UNION ALL SELECT '20110401'
UNION ALL SELECT '20110404'
UNION ALL SELECT '20110407'
UNION ALL SELECT '20110409'
UNION ALL SELECT '20110411'
UNION ALL SELECT '20110412'
UNION ALL SELECT '20110413'
UNION ALL SELECT '20110414'
UNION ALL SELECT '20110419'
UNION ALL SELECT '20110420'
UNION ALL SELECT '20110425'
UNION ALL SELECT '20110427'
UNION ALL SELECT '20110428'
UNION ALL SELECT '20110430'
UNION ALL SELECT '20110502'
UNION ALL SELECT '20110505'
UNION ALL SELECT '20110507'
UNION ALL SELECT '20110510'
UNION ALL SELECT '20110513'
UNION ALL SELECT '20110515'
UNION ALL SELECT '20110516'
UNION ALL SELECT '20110521'
UNION ALL SELECT '20110523'
UNION ALL SELECT '20110524'
UNION ALL SELECT '20110525'
UNION ALL SELECT '20110527'
UNION ALL SELECT '20110528'
UNION ALL SELECT '20110530'
UNION ALL SELECT '20110601'
UNION ALL SELECT '20110602'
UNION ALL SELECT '20110607'
UNION ALL SELECT '20110609'
UNION ALL SELECT '20110610'
UNION ALL SELECT '20110615'
UNION ALL SELECT '20110617'
UNION ALL SELECT '20110618'
UNION ALL SELECT '20110620'
UNION ALL SELECT '20110621'
UNION ALL SELECT '20110623'
UNION ALL SELECT '20110626'
UNION ALL SELECT '20110628'
UNION ALL SELECT '20110629'
UNION ALL SELECT '20110630'
UNION ALL SELECT '20110701'
UNION ALL SELECT '20110706'
UNION ALL SELECT '20110707'
UNION ALL SELECT '20110708'
UNION ALL SELECT '20110715'
UNION ALL SELECT '20110717'
UNION ALL SELECT '20110721'
UNION ALL SELECT '20110722'
UNION ALL SELECT '20110727'
UNION ALL SELECT '20110729'
UNION ALL SELECT '20110801'
UNION ALL SELECT '20110810'

SELECT 
   CAST(RD.CompletionDate AS datetime) AS casts_fine
FROm
   @REAL_DATES RD

我很欣賞關於 SSIS 或其他方法的評論，但在遊戲的這一點上，我們已經與 TSQL 轉換方法結合了。如果有人能指出我遺漏了什麼，我會以你的名字命名我的第一個孩子，假設你不介意把你的名字改成詹姆斯。

當你這樣做時會發生什麼？

DECLARE @REAL_DATES TABLE
(
   CompletionDate VARCHAR(46)
);

INSERT INTO
   @REAL_DATES
   SELECT CompletionDate
   FROM dbo.Entity;

SELECT 
   CAST(RD.CompletionDate AS datetime) AS casts_fine
   FROM @REAL_DATES RD;

我在 twitter 上得到的是優化器可以在消除行之前嘗試轉換，因此您不能只考慮連接返回的 CompletionDate 值。

我的第一個建議是使用正確的數據類型。為什麼要使用 VARCHAR(46) 來儲存日期？這就是為什麼表中有錯誤數據以及為什麼當您想要不是字元串的豐富數據時必須顯式轉換的原因（恕我直言，首先不應該是字元串）。

我的下一個建議是更正該列中的所有數據，並採取措施使其不會再次失效。例如，驗證ISDATE(columnname) = 1.

如果這兩個失敗，我列表中的下一個是將數據返回給客戶端並讓它轉換為日期時間或顯示或你有什麼。無論您在哪裡過濾掉導致問題的行，優化器都可以推動該評估，以便在清除壞行之前嘗試轉換。

最後，您可以將查詢結果轉儲到臨時表/表變數中，並在查詢該中間對象時作為第二步執行轉換（因為您應該確信這裡的日期是有效的 - 實際上您可以檢查首先，如果您的聯接碰巧返回了一些日期無效的行，則會引發錯誤）。

底線：（a）您不能對將在堆棧中的哪個位置進行轉換嘗試做出任何假設，並且（b）如果您使用正確的數據類型，則不需要這些變通方法和黑客攻擊。

引用自：https://dba.stackexchange.com/questions/5312

相關問答

Sql-Server

更改 DateTime 欄位的時間

  • May 8, 2020
檢視問答
Sql-Server

將日期時間列轉換為秒數

  • July 9, 2019
檢視問答
Sql-Server

將日期時間轉換為歐洲預設沒有時間

  • January 10, 2018
檢視問答
Sql-Server

如何選擇有效的最大日期？

  • May 29, 2022
檢視問答
Sql-Server

如何檢查 null 或空表值參數？

  • March 2, 2022
檢視問答
Sql-Server

如果為正，則對所有項目求和。如果為負，則返回每一個

  • January 18, 2022
檢視問答