Converting To Mm/dd/yyyy Format

I have a table called SF_Data and there is a column called IN_Date, ID the data looks like: ID IN_Date 1 9/8/2010 2 26/04/2011 3 20/09/2010 The datatatype o

Solution 1:

You need a convert to fix the data (to the correct datatype) before formatting...

Selectconvert(varchar,
         convert(date, IN_Date, 103),
     101)
from dbo.SF_Data

Solution 2:

The 3rd parameter to convert has no meaning when converting from varchar to varchar. So per @marc_s' comment, you'd have to convert the varchar to a datetime using the 103 format, and then from datetime to varchar specifying the 101 format:

Selectconvert(varchar(12),convert(datetime,IN_Date,103),101) From dbo.SF_Data

For example:

selectconvert(varchar(12),convert(datetime,'31/12/2001',103),101)

prints 12/31/2001.

See MSDN.