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 of IN_Date is varchar(50).
I am trying to convert the IN_Date to mm/dd/yyyy format. I tried doing this:
Select convert(varchar,IN_Date,103) From dbo.SF_Data
But still the format doesn't change. Can anyone tell me where I am going wrong
You need a convert to fix the data (to the correct datatype) before formatting...
Select convert(varchar, convert(date, IN_Date, 103), 101) from dbo.SF_Data
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:
Select convert(varchar(12),convert(datetime,IN_Date,103),101) From dbo.SF_Data