Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

@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. – M.S.Nayak Jan 19 '12 at 12:43
I'm closing this as too basic for, which is for "all advanced database-related topics" – Jack Douglas Jan 19 '12 at 13:43
Why bother closing the question? People have answered and voted for it. I disagree that it is off topic. – datagod Feb 6 '12 at 1:11
up vote 5 down vote accepted

This is string manipulation: nothing to do with datetimes

   SUBSTRING(Whatever, 4,2) + '-' + LEFT(Whatever, 2) + '-' + RIGHT(Whatever, 4)

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.