I've question when I've date as string in a single column of table on that date basis I wish to sort the table in to the database. How do i perform this operation?
create table dateTable ( _id INTEGER, date varchar2(50), name varchar2(50) );
The DBMS is Oracle, in the event that matters.
Q: so maybe there is multiple formats for date, say "dd-mm-yyyy" or "mm-dd-yyyy" within the date area?
A: Yes, the consumer can key in any format.
Add yet another column and place format from the date value. Use that column within the order by clause by transforming the varchar up to now 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 amounts in number data types, strings in character data types and dates in date datatypes. Should you choose it every other way you do in wrong and you'll be working from the database, which will appear to operate against you. (that we believe just happened).
For your question, you simply order through the date column. When the rows emerge within the "wrong" order, browse the previous paragraph again.
select * from dateTable order by date;
choose * from dateTable ORDERBy to_dateto_date(date, 'yyyymmdd')
or choose * from dateTable ORDERBy to_dateto_date(date)
The syntax for that to_date function is:
to_date( string1, [ format_mask ], [ nls_language ] )
string1 may be the string that'll be transformed into to start dating ?.
format_mask is optional. This is actually the format that'll be accustomed to convert string1 to some date.
nls_language is optional. This is actually the nls language accustomed to convert string1 to some date.
You cannot sort to start dating ? in varchar format should you allow 'any format'. Consider adding another column having a calculated value having a specified fix format.
You cannot sort the database as you would like. You must have some kind format for sorting because it can't sort by all formats.
DB type -> Oracle or MSSQL?
Ok! If i have tried personally date datatype then and if i wish to accept different date formats then so what can i actually do in that situation
It all depends greatly around the language youre using for that application. Normally you'd determine the date format in the customers locale in your internationalization/localization infrastructure. And therefore the date format used is particularly associated with the locale from the user (ie. en_US, en_GB, p_P, etc..). If you wish to accept any format from anybody without needing a locale or user designated format, you will want to create an formula to try to guess the format, and that i dont think there's likely to be in whatever way to create that formula full proof.
Its only a bad design decision not to make use of a single date format within the database. If you wish to accept multiple formats from user's thats an ordinary and fine factor to complete but you ought to be handling conversion towards the DB format around the application side before placing or utilizing it to question.
HI do this directly in query.. Its employed by my application
order by m.produced_on desc LIMIT ,5