how to convert date to a format `mm/dd/yyyy`

I’m having a sql table with date column named CREATED_TS which holds the dates in different format eg. as shown 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

​x
 
Feb 20 2012 12:00AM11/29/12  8:20:53 PM          Feb 20 2012 12:00AM11/29/12  8:20:53 PM          Feb 20 2012 12:00AM11/29/12  8:20:53 PM          Nov 16 2011 12:00AMFeb 20 2012 12:00AM11/29/12  8:20:52 PM​

Now I want to convert these to format mmddyyyy before as i am comparing the dates in WHERE clause of my SELECT query.

I tried using

CONVERT(VARCHAR(10),CREATED_TS,101)

 
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

 
Feb 20 20111/29/12  Feb 20 20111/29/12  Feb 20 20111/29/12  Nov 16 201Feb 20 20111/29/12  ​

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

Any help will be appreciated.

Answer

As your data already in varchar, you have to convert it into date first:

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

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

Advertisement

Answer