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?

Possible duplicate of [Conversion failed when converting date and/or time from character string while inserting datetime](https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) — VDWWD, Jul 23 '18 at 06:32
bad idea to store date in `varchar` data type. Should use `date` or `datetime` — Squirrel, Jul 23 '18 at 06:32
[Bad habits to kick: choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) — Damien_The_Unbeliever, Jul 23 '18 at 06:48

score 3 · Accepted Answer · answered Jul 23 '18 at 09:32

3

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

answered Jul 23 '18 at 09:32

Ravi

1,157
1
9
19

score 1 · Answer 2 · answered Jul 23 '18 at 06:37

1

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.

answered Jul 23 '18 at 06:37

Squirrel

23,507
4
34
32

Style 103 gives the same error unfortunately. Tried many styles but none work – Brian Mogambi Jul 23 '18 at 07:02
probably you have some rows that the format is not `DD/MM/YYYY`. Try verify your data. Or simply use string comparison. – Squirrel Jul 23 '18 at 07:21

score 1 · Answer 3 · answered Jul 23 '18 at 09:47

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

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

3 Answers3