Convert YYYY-MM-DD to MM/DD/YYYY

I have this DOB column in my additional information table with YYYY-MM-DD. I need to convert let say DOB: 1949-06-15 to DOB format: MM/DD/YYYY.

I have

SELECT CONVERT(VARCHAR(10), ai.ADDLINFO_INDEX_21, 101) as 'DOB' 

but I still get this result: 1949-06-15.

I even tried

REPLACE(CONVERT(VARCHAR, ai.ADDLINFO_INDEX_21, 101), '-', '/') 

However, the result is 1949/06/15.

I am looking for 06/15/1949 as the output. Can someone please help?

2 Answers

From the comments, it appears you're saving the string equivalent of the date. You could cast the string to the date datatype, then use the convert function for the format output. For example:

DECLARE @mydate char(10) = '1949-06-15' SELECT CONVERT(varchar(10), CAST(@mydate as date), 101) 

Produces:

06/15/1949 

You will need to use REPLACE and CONVERT functions. Please try this:

SELECT REPLACE(CONVERT(varchar(10), CONVERT(datetime, '2010-02-11', 120), 101), '/', '-') 

Edit: Just noticed, you want / instead of - in your output seems like. In this case, please just remove replace, like:

SELECT CONVERT(varchar(10), CONVERT(datetime, '2010-02-11', 120), 101) 

P.S. Assumed that you are using Sql Server 2008.

Edit 2: Since you are receiving the date value from an attribute of another table, you will need to use something like below:

SELECT CONVERT(varchar(10), CONVERT(datetime, ai.ADDLINFO_INDEX_21, 120), 101) from ai 

Assuming ai is the table name. If it is not, use the table name with the alias of ai, like:

from tablename ai