How to convert date to a format mm dd yyyy

Question

I have a SQL table with a date field entitled CREATED TS that contains dates in various formats, such as. as depicted below:

Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Nov 16 2011 12:00AM
Feb 20 2012 12:00AM
11/29/12  8:20:52 PM

Now that I'm comparing the dates in the WHERE clause of my SELECT query, I want to change these to the format mmddyyyy before.

I tried using:

CONVERT(VARCHAR(10),CREATED_TS,101)

But got the result as,

Feb 20 201
11/29/12  
Feb 20 201
11/29/12  
Feb 20 201
11/29/12  
Nov 16 201
Feb 20 201
11/29/12  

I need the result eg. 02/20/2012 in order to compare. 

Answer 1

Your data is already in varchar, thus you must first convert it to date:

select convert(varchar(10), cast(ts as date), 101) from <your table>

I hope this helps you.

Answer 2

My date of birth change