Conversion failed when converting date and/or time from character string: trx_date

Question

I'm getting the error

Conversion failed when converting date and/or time from character string.

when I execute the query below in SQL Server 2012:

SELECT DISTINCT 
    payment_date, 
    CONVERT(varchar, CAST(amount AS money), 1) amount, 
    trx_date 
FROM vi_mpesa_payments2
WHERE bulk_center = 'LONDON'
AND (cast(convert(date,[trx_date],3) as varchar(30)) = '2018-07-23')

The value of trx_date is '23/07/2018' of type varchar, which I'm converting to date and changing the date format then casting it to varchar in order to do the comparison. This should work but it doesn't, why?

Answer 1

try this one

SELECT DISTINCT 
payment_date, 
CONVERT(varchar, CAST(amount AS money), 1) amount, 
trx_date 
FROM vi_mpesa_payments2
WHERE bulk_center = 'LONDON'
AND 
(convert(datetime, [trx_date], 103) = cast('2018-07-23' as date))

Answer 2

to simply resolve your error, just change style to 103. Do note that style 3 is DD/MM/YY without the century

If the trx_date is varchar, why not just simply use compare directly without converting

AND [trx_date] = '23/07/2018'

Anyway, you should store date in date or datetime data type.

Answer 3

You can use try_convert() function & i would suggest to identity your varchar date format using :

select *
from vi_mpesa_payments2
where try_convert(date, trx_date, 103) is null;

Then you can use :

SELECT DISTINCT payment_date, CONVERT(varchar, CAST(amount AS money), 1) amount, 
       o_trx_date AS trx_date
FROM vi_mpesa_payments2 CROSS APPLY
     ( VALUES try_convert(date, trx_date, 103)
     ) t (o_trx_date)
WHERE bulk_center = 'LONDON' AND
      o_trx_date = '2018-07-23';