Convert to mm / dd / yyyy format

Question

I have a table called SF_Data, and there is a column called IN_Date, ID, which looks like this:

ID IN_Date 1 9/8/2010 2 26/04/2011 3 20/09/2010 

The data type IN_Date is varchar (50).

I am trying to convert IN_Date to mm / dd / yyyy format. I tried to do this:

 Select convert(varchar,IN_Date,103) From dbo.SF_Data 

But the format does not change. Can someone tell me where I am going wrong?

Answer 1

You need to convert to fix the data (to the desired data type) before formatting ...

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

Answer 2

The third option to convert does not make sense when converting from varchar to varchar . So in the @marc_s comment, you will need to convert varchar to datetime using format 103, and then from datetime to varchar with format 101:

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

For instance:

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

displays 12/31/2001 .

See MSDN.