Conversion failed when converting date and/or time from character string. SQL query-CodePudding

I have the following SQL query:

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')

which produces the following results:

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

When I try to put all the results into new table I get the following error:

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')

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

What could be the reason? Thanks!

CodePudding user response：

Here is the problem, if you are trying to convert strings to a date and some of the strings in the table cannot be converted to a date, SQL Server might still try to convert them even if you have filters that should eliminate them. This is because SQL Server is free to perform the conversion before the filter or the filter before the conversion depending on the plan chosen (which explains why the select alone might behave differently than a select into), and you have very little control over this. What you can do is change:

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

To:

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

You don't need a style number when converting GETDATE() because it isn't stored as some regional, ambiguous string like m/d/y. In fact you don't need to convert it at all; regardless of what time it is today, DATEDIFF will yield the same result if you trim time or not, so:

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

You may even be able to simplify and get rid of the CASE expression altogether, since the output doesn't really matter if these rows are all filtered out in the end; this will yield NULL for bad rows anyway:

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