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

asp.netsqlsql server

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?

Best Answer

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))
Related Topic