Varchar to date in SQL Server 2008 [closed]

Question

I have varchar data in database table which stores date in DD-MM-YYYY format.

I want to select that varchar in MM-DD-YYYY format.

I'm using

select convert (varchar(30), (select CONVERT(datetime, '16-11-2011', 103)), 101)

It gives 11/16/2011

Any simple query is possible for this?

@gbn No, I have to use a database which is designed by other who had declared the field as varchar if i change something the software using that database will give some error so. and I want MM-DD-YYYY format in front end it may be date or varchar.
I'm closing this as too basic for dba.se, which is for "all advanced database-related topics"
Why bother closing the question? People have answered and voted for it. I disagree that it is off topic.

gbn · Accepted Answer · 2012-01-19 12:56:12Z

up vote 5 down vote accepted

This is string manipulation: nothing to do with datetimes

SELECT
   SUBSTRING(Whatever, 4,2) + '-' + LEFT(Whatever, 2) + '-' + RIGHT(Whatever, 4)
FROM
   SomeTable;

If you stored it as datetime, then you'd format it into whatever locale you wanted on the client. Then it'd be sortable and comparable too.

edited Jan 19 '12 at 12:56

answered Jan 19 '12 at 12:40

gbn
42.5k353105

closed as off topic by Jack Douglas♦ Jan 19 '12 at 13:41