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

Answers


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

For example:

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

prints 12/31/2001.

See MSDN.


Need Your Help

How does JavaScript handle objects passed to functions with regards to this linked list example?

javascript function object reference

I'm noticing behavior I don't understand in JavaScript. I understand that with regards to primitive types when passed to a function, they're passed by value. If we pass an object, they're passed by

How Does CodeIgniter Verify Session Data?

php codeigniter session cookies codeigniter-2

There is a similar question already asked but none provided the right answer so I will go a bit more in depth to make sure we understand. PHP sessions store a server-side session ID that will be co...