Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
    
@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 dba.se, which is for "all advanced database-related topics" –  Jack Douglas Jan 19 '12 at 13:43
1  
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
add comment

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.

1 Answer

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.

share|improve this answer
add comment

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