oracle 如何使用日期字段对数据库进行排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4374013/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort database using date field?
提问by Nikki
I have question 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.
DBMS 是 Oracle，如果这很重要的话。
Q: so will there be multiple formats for date, say "dd-mm-yyyy" or "mm-dd-yyyy" in the date field?
问：那么日期是否会有多种格式，在日期字段中说“dd-mm-yyyy”或“mm-dd-yyyy”？
A: Yes, the user can type in any format.
答：是的，用户可以输入任何格式。
回答by kewljibin
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)"
再添加一列并插入日期值的格式。通过使用“to_date(date_value,date_format)”将varchar转换为日期，在order by子句中使用该列
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
回答by Ronnis
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 seemto 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;
回答by Jaykay
select * from dateTable ORDERBy to_dateto_date(date, 'yyyymmdd');
select * from dateTable ORDERBy to_dateto_date(date, 'yyyymmdd');
or select * from dateTable ORDERBy to_dateto_date(date);
或 select * from dateTable ORDERBy to_dateto_date(date);
The syntax for the to_date function is:
to_date 函数的语法是：
to_date( string1, [ format_mask ], [ nls_language ] )
string1 is the string that will be converted to a date.
string1 是将转换为日期的字符串。
format_mask is optional. This is the format that will be used to convert string1 to a date.
format_mask 是可选的。这是将用于将 string1 转换为日期的格式。
nls_language is optional. This is the nls language used to convert string1 to a date.
nls_language 是可选的。这是用于将 string1 转换为日期的 nls 语言。
回答by bw_üezi
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.
如果允许“任何格式”，则不能以 varchar 格式对日期进行排序。尝试添加具有指定修复格式的计算值的第二列。
回答by bw_üezi
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.
您无法根据需要对数据库进行排序。您需要使用某种类型格式进行排序，因为它无法按所有格式排序。
回答by prodigitalson
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.
这在很大程度上取决于您用于应用程序的语言。通常，您将根据用户区域设置确定日期格式，作为国际化/本地化基础结构的一部分。这意味着所使用的日期格式与用户的区域设置相关（即 en_US、en_GB、de_DE 等）。如果您想在不使用语言环境或用户指定格式的情况下接受任何人的任何格式，那么您需要编写一个算法来尝试猜测格式，我认为没有任何方法可以使该算法完全证明。
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.
在数据库中不使用单一日期格式只是一个糟糕的设计决定。如果您想接受来自用户的多种格式，这是一件正常而好的事情，但您应该在插入或使用它进行查询之前在应用程序端处理转换为 DB 格式。
回答by keval dadia
HI try this directly in query.. Its working for my application
HI 直接在查询中尝试这个。它适用于我的应用程序
order by m.created_on desc LIMIT 0,5
按 m.created_on desc LIMIT 0,5 排序
