Pablo Gûereca Pablo Gûereca - 1 year ago 74
SQL Question

varchar values to date format

in sql server 2014 I have a column with multiple varchar formats:-

shipdate
12/29/2014 -- mm/dd/yyyy
5/20/15 -- mm/dd/yy
2012/05/22 -- yyyy/mm/dd


I would like to convert them to YYYY/MM/DD date format but I can't, I tried with:-

CAST(SRD.[ShipDate] AS DATEtime)


without success.

Any idea?

Answer Source

Hmmmm . . . You really want to store dates as dates. In your case, you can use try_convert() and keep testing until it works:

select coalesce(try_convert(date, shipdate),
                try_convert(date, shipdate, 1),
                try_convert(date, shipdate, 101)
               ) as presumed_date
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download