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

Question

I want date to be displayed as yyyy-mm-dd. The value in the table for date is dd/mm/yyyy. I have run the query

select CONVERT(DATE, FORMDATE, 103) from UPDATELOG

and i got error "Conversion failed when converting date and/or time from character string". Anyone can help me with this?

Know your RDBMS. mysql <> sql-server. This, however, looks like T-SQL as CONVERT has a style code. Please update your tags, but also add at least a couple of sample values. — Thom A
– Thom A ♦, Commented Apr 12, 2018 at 15:09
It means you have bad date in your data. Not all the dates are in dd/mm/yyyy format. Which version of sql server you are using ? Also stop storing dates as strings to avoid such problems in future — Pரதீப்
– Pரதீப், Commented Apr 12, 2018 at 15:11
The root of your challenge here is that you are storing dates as strings. You should always use the date/datetime datatypes when storing dates. That is why those datatypes exist. sqlblog.org/2009/10/12/… — Sean Lange
– Sean Lange, Commented Apr 12, 2018 at 15:12

Gordon Linoff · Accepted Answer · 2018-04-12 15:18:31Z

1

This certainly looks like SQL Server. So, use try_convert() instead:

select TRY_CONVERT(DATE, FORMDATE, 103)
from UPDATELOG  ;

To get the values that fail, use:

select FORMDATE
from UPDATELOG
where TRY_CONVERT(DATE, FORMDATE, 103) is null

answered Apr 12, 2018 at 15:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1