Rohaan Rohaan - 8 days ago 5
SQL Question

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


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

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 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), 111) from <your table>