CONVERTING varchar to date

Transact-SQL
1

I have a varchar(512) data type on my INVOICE_DATE field and it is in the following format DD/MM/YYYY.
I need it to be in MM/DD/YYYY and have tried with no luck. Suggestions?

SELECT CONVERT(VARCHAR(512), INVOICE_DATE, 101) AS [MM/DD/YY]
FROM F0AInvoices_Tags

This does not swap the numbers as I would have hoped for reporting purposes.

2 
CONVERT(VARCHAR(512),CONVERT(DATE,INVOICE_DATE,103),101)

If you don't have to convert it to string, it would be preferable to store as DATE or DATETIME data type.

3

Thanks for your response.

When I run this it gives me conversion failed when converting date and/or time from character string.

SELECT CONVERT(VARCHAR(512),CONVERT(DATE,INVOICE_DATE,103),101)
FROM F0AInvoices_Tags

4

That means there are rows in your table that are not in the DD/MM/YYYY format.

Try this - and it should fail.

SELECT CONVERT(DATE,INVOICE_DATE,103)

If you are on SQL 2012 or later, run this to see the offending rows:

select INVOICE_DATE
from F0AInvoices_Tags
where  TRY_CONVERT(DATE, INVOICE_DATE, 103) is null;