How to sort database using date field?

I have ques开发者_开发问答tion that if I have date as string in one column of table on that date basis I want to sort the table into the database. How can I perform this operation?

create table dateTable (
    _id INTEGER, 
    date varchar2(50), 
    name varchar2(50)
);

The DBMS is Oracle, if that matters.

Q: so will there be multiple formats for date, say "dd-mm-yyyy" or "mm-dd-yyyy" in the date field?

A: Yes, the user can type in any format.

---

Add one more column and insert format of the date value. Use that column in the order by clause by converting the varchar to date using "to_date(date_value,date_format)"

create table dateTable (date_id INTEGER, date_value varchar2(50),
date_name varchar2(50),date_format varchar2(50));

insert into dateTable values (1,'20101212121212','date1','YYYYMMDDHH24MISS');
insert into dateTable values (1,'20101212','date1','YYYYMMDD');
insert into dateTable values (1,'20101213','date3','YYYYMMDD');

select * from dateTable
order by to_date(date_value,date_format) desc

---

Store numbers in numeric data types, strings in character data types and dates in date datatypes. If you do it any other way you are doing in wrong and you will be working against the database, which in turn will seem to work against you. (which I believe just happened).

As for your question, you just order by the date column. If the rows come out in the "wrong" order, read the previous paragraph again.

select *
  from dateTable 
 order by date;

---

select * from dateTable ORDERBy to_dateto_date(date, 'yyyymmdd');

or select * from dateTable ORDERBy to_dateto_date(date);

The syntax for the to_date function is:

to_date( string1, [ format_mask ], [ nls_language ] )

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

---

You can't sort a date in varchar format if you allow 'any format'. Try to add a second column with a computed value with a specified fix format.

---

You can't sort the database as you want. You need to have some type format for sorting as it can't sort by all formats.

---

Ok! If i have used date datatype then also if i want to accept different date formats then what can i do in that situation

This will depend greatly on the language youre using for the application. Normally you would determine the date format from the users locale as part of your internationalization/localization infrastructure. Meaning that the date format used is specifically tied to the locale of the user (ie. en_US, en_GB, de_DE, etc..). If you want to accept any format from anyone without using a locale or user designated format, then you need to write an algorithm to attempt to guess the format, and i dont think there is going to be any way to make that algorithm full proof.

---

Its just a bad design decision to not use a single date format in the database. If you want to accept multiple formats from user's thats a normal and fine thing to do but you should be handling conversion to the DB format on the application side before inserting or using it to query.

---

HI try this directly in query.. Its working for my application

order by m.created_on desc LIMIT 0,5