從字串轉換日期和/或時間時轉換失敗。SQL查詢-有解無憂

我有以下 SQL 查詢：

SELECT D.name, X.displayName, x.diff, X.tacticalParameter_id 

FROM (
        select displayName, tacticalParameter_id, b.value,datediff(DAY,convert(date,b.value,101),convert(date,getdate(),101)) as diff from EDA_TENANT1.EDA_T_P_A A, eda_tenant1.EDA_T_P_A_V b
        where b.attribute_id = A.ID
        and displayName like 'Fecha %'
        and value <> '') X,  
    EDA_TENANT1.EDA_T_P C, EDA_TENANT1.EDA_TACT_P_G D
    WHERE X.tacticalParameter_id = C.id
    AND C.tacticalParameterGroup_id = D.id
    and D.NAME in ('TPG Codigo postal - SPG Codigo postal',
    'TPG Cuenta bancaria - SPG Cuenta bancaria',
    'TPG Direccion - SPG Direccion',
    'TPG Doc identidad - SPG Doc identidad',
    'TPG Email - SPG Email',
    'TPG Nombre - SPG Nombre',
    'TPG Nombre empresa - SPG Nombre empresa',
    'TPG Telf empresa - SPG Telf empresa',
    'TPG Telf empresa movil - SPG Telf empresa movil',
    'TPG Telf fijo - SPG Telf fijo',
    'TPG Telf movil - SPG Telf movil',
    'TPG Codigo agente - SPG Codigo agente',
    'TPG Fiscal num - SPG Fiscal num')

這會產生以下結果：

name    displayName diff    tacticalParameter_id
TPG Email - SPG Email   Fecha purga email   -245    48e221cc-6628-4e6b-880f-d68d0f074d72
TPG Email - SPG Email   Fecha purga email   -294    78455c15-4f60-433e-a687-121a4098baeb
TPG Nombre - SPG Nombre Fecha purga nombre y apellidos  -287    52227b6b-7497-4533-93c1-72c736175ef8
TPG Telf movil - SPG Telf movil Fecha purga telefono 2  -308    e8c465f9-cf74-4fe8-89ee-41d618055ab7
TPG Fiscal num - SPG Fiscal num Fecha purga doc identidad   -189    ed41d6a1-1c77-48d7-bfb7-eafac767084b
TPG Telf movil - SPG Telf movil Fecha purga telefono 2  -266    e7d500f2-1971-41f2-a24c-00edc7b2c7f0
TPG Email - SPG Email   Fecha purga email   -161    81593640-f080-4d11-afae-c822c4a51f05
TPG Email - SPG Email   Fecha purga email   -77 fcb880f8-cb3d-4180-9c7c-01d7cdb04022

當我嘗試將所有結果放入新表中時，出現以下錯誤：

SELECT D.name, X.displayName, x.diff, X.tacticalParameter_id 

  into kk_aux   -- only change

FROM (
        select displayName, tacticalParameter_id, b.value,datediff(DAY,convert(date,b.value,101),convert(date,getdate(),101)) as diff from EDA_TENANT1.EDA_T_P_A A, eda_tenant1.EDA_T_P_A_V b
        where b.attribute_id = A.ID
        and displayName like 'Fecha %'
        and value <> '') X,  
    EDA_TENANT1.EDA_T_P C, EDA_TENANT1.EDA_TACT_P_G D
    WHERE X.tacticalParameter_id = C.id
    AND C.tacticalParameterGroup_id = D.id
    and D.NAME in ('TPG Codigo postal - SPG Codigo postal',
    'TPG Cuenta bancaria - SPG Cuenta bancaria',
    'TPG Direccion - SPG Direccion',
    'TPG Doc identidad - SPG Doc identidad',
    'TPG Email - SPG Email',
    'TPG Nombre - SPG Nombre',
    'TPG Nombre empresa - SPG Nombre empresa',
    'TPG Telf empresa - SPG Telf empresa',
    'TPG Telf empresa movil - SPG Telf empresa movil',
    'TPG Telf fijo - SPG Telf fijo',
    'TPG Telf movil - SPG Telf movil',
    'TPG Codigo agente - SPG Codigo agente',
    'TPG Fiscal num - SPG Fiscal num')

訊息 241，級別 16，狀態 1，第 1 行
從字串轉換日期和/或時間時轉換失敗。

可能是什么原因？謝謝！

uj5u.com熱心網友回復：

這就是問題所在，如果您嘗試將字串轉換為日期并且表中的某些字串無法轉換為日期，SQL Server 可能仍會嘗試轉換它們，即使您有應該消除它們的過濾器。這是因為 SQL Server 可以根據選擇的計劃自由地在過濾器之前執行轉換或在轉換之前執行過濾器（這解釋了為什么單獨的 select 可能與 select into 的行為不同），而您對此幾乎沒有控制權。你能做的就是改變：

datediff(DAY,convert(date,b.value,101),convert(date,getdate(),101))

到：

CASE WHEN TRY_CONVERT(date, b.value, 101) IS NOT NULL
  THEN DATEDIFF(DAY, TRY_CONVERT(date, b.value, 101),
    CONVERT(date,GETDATE())) END

轉換時不需要樣式編號，GETDATE()因為它沒有存盤為一些區域性的、模棱兩可的字串，例如m/d/y. 事實上，您根本不需要轉換它；無論今天是什么時間，無論DATEDIFF是否修剪時間，都會產生相同的結果，因此：

CASE WHEN TRY_CONVERT(date, b.value, 101) IS NOT NULL
  THEN DATEDIFF(DAY, TRY_CONVERT(date, b.value, 101),
    GETDATE()) END

您甚至可以簡化并CASE完全擺脫運算式，因為如果這些行最終都被過濾掉，輸出并不重要；NULL無論如何，這將產生壞行：

DATEDIFF
(
    DAY, 
    TRY_CONVERT(date, b.value, 101),
    GETDATE()
)

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/476852.html

標籤：sql sql服务器

上一篇：SQL查詢以獲取每個狀態的總持續時間

下一篇：如何創建索引以優化“鍵查找”