如何在 Oracle 中格式化和排序日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7093743/
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 format and sort a date in Oracle?
提问by Hariharbalaji
In my application am trying to format and sort the date, i am using to_char()function to format the date to my required format, but when i sort them it is sorting it as string sorting. But i want them to be sorted as date.
在我的应用程序中，我正在尝试对日期进行格式化和排序，我正在使用to_char()函数将日期格式化为我需要的格式，但是当我对它们进行排序时，它会将其排序为字符串排序。但我希望它们按日期排序。
I need some help to achieve both in the same query. Kindly help me on the same.
我需要一些帮助才能在同一个查询中实现这两个目标。请帮助我。
The query which i used was,
我使用的查询是，
SELECT to_char( t1.your_date_column1, your_format_mask ) as alias,
FROM your_table1 t1,your_table2
ORDER BY t1.your_date_column1
回答by Justin Cave
It sounds like you want something like
听起来你想要类似的东西
SELECT to_char( your_date_column, your_format_mask )
FROM your_table
ORDER BY your_date_column
In the SELECTlist, you want to return a character string that represents the date in your preferred format. In the ORDER BYclause, you want to order by the actual date. Using the standard EMPand DEPTtables, for example
在SELECT列表中，您希望以您的首选格式返回一个表示日期的字符串。在ORDER BY子句中，您希望按实际日期排序。使用标准EMP和DEPT表格，例如
SQL> ed
Wrote file afiedt.buf
1 select to_char( hiredate, 'DD-MM-YYYY' )
2 from emp,
3 dept
4 where emp.deptno = dept.deptno
5* order by hiredate
SQL> /
TO_CHAR(HI
----------
17-12-1980
20-02-1981
22-02-1981
02-04-1981
01-05-1981
09-06-1981
08-09-1981
28-09-1981
17-11-1981
03-12-1981
03-12-1981
23-01-1982
19-04-1987
23-05-1987
14 rows selected.
If you add a DISTINCT, the problem is that Oracle doesn't know that the function you are applying (in this case TO_CHAR) provides a one-to-one mapping from the data in the table to the data in the output. For example, two different dates (October 1, 2010 10:15:15 and October 1, 2010 23:45:50) might generate the same character output, forcing Oracle to eliminate one of the two '01-10-2010' strings but the two dates would sort differently. You can rectify that problem by nesting your query and converting the string back to a date after doing the DISTINCTand before doing the ORDER BY
如果添加 DISTINCT，问题在于 Oracle 不知道您正在应用的函数（在本例中为 TO_CHAR）提供从表中的数据到输出中的数据的一对一映射。例如，两个不同的日期（2010 年 10 月 1 日 10:15:15 和 2010 年 10 月 1 日 23:45:50）可能会生成相同的字符输出，从而迫使 Oracle 消除两个 '01-10-2010' 字符串之一但两个日期的排序方式不同。您可以通过嵌套查询并做后转换的字符串返回日期纠正这个问题DISTINCT，做之前ORDER BY
SQL> ed
Wrote file afiedt.buf
1 select hire_date_str
2 from (
3 select distinct to_char( hiredate, 'DD-MM-YYYY' ) hire_date_str
4 from emp,
5 dept
6 where emp.deptno = dept.deptno
7 )
8* order by to_date(hire_date_str,'DD-MM-YYYY')
SQL> /
HIRE_DATE_
----------
17-12-1980
20-02-1981
22-02-1981
02-04-1981
01-05-1981
09-06-1981
08-09-1981
28-09-1981
17-11-1981
03-12-1981
23-01-1982
19-04-1987
23-05-1987
13 rows selected.
回答by Steve
SELECT
to_char( your_date_column, your_format_mask ) as formate_purpose,
FROM your_table
ORDER BY to_date (formate_purpose)
Try the above code
试试上面的代码
回答by Hardik Bhalani
The easiest way is to retrieve the same field with the query again and doing sorting based upon that filed
最简单的方法是再次使用查询检索相同的字段并根据该字段进行排序
In your example
在你的例子中
SELECT
to_char( your_date_column, your_format_mask ) as formate_purpose,
your_date_column as sorting_purpose
FROM your_table
ORDER BY your_date_column
回答by Tony Andrews
You don't say what your application is written in, but in some environments (e.g. Oracle APEX, Oracle Reports) the solution is to notuse to_charin the query, but then to apply the desired formatting in the tool's "column properties" or similar.
您没有说明您的应用程序是用什么编写的，但在某些环境（例如 Oracle APEX、Oracle Reports）中，解决方案不是to_char在查询中使用，而是在工具的“列属性”或类似的应用程序中应用所需的格式.
回答by Erich Kitzmueller
If you let Oracle sort (recommended), just do it like described in Justin Cave's answer. If, for some reason, you do the sorting in Java, do not use to_char; get the dates as Dateobjects instead and use e.g. a SimpleDateFormatto do the formatting in Java (after sorting).
如果您让 Oracle 排序（推荐），请按照 Justin Cave 的回答中所述进行操作。如果出于某种原因，您在 Java 中进行排序，请不要使用to_char; 将日期作为Date对象获取，并使用例如 aSimpleDateFormat在 Java 中进行格式化（排序后）。
回答by Chris Panagopoulos
For sqlplus, use alter session set nls_date_format to what you want the date format to be, then just use the column name in your select statement and sort order.
对于 sqlplus，使用 alter session set nls_date_format 设置为您想要的日期格式，然后只需在选择语句和排序顺序中使用列名。
回答by dave mc
I wanted to Group By and Order By the Date field but the Date field included the Time and I didn't want to include the Time in the grouping and sorting. So I converted the Date to Character and then converted the character back to Date to eliminate the Time and sort by date not by text. That grouped the data by the date and sorted by the date.
我想按日期字段分组和排序，但日期字段包括时间，我不想在分组和排序中包括时间。所以我将日期转换为字符，然后将字符转换回日期以消除时间并按日期而不是文本排序。将数据按日期分组并按日期排序。
-- Projects initiated by Day.
select to_date(to_char(psd.PROJECTSTATUSDATE, 'mm/dd/yyyy'),'mm/dd/yyyy') as "Date", count(*)
from project pj, project_status_date psd
where PJ.PROJECTTOKEN = PSD.PROJECTTOKEN
and psd.PROJECTSTATUSDATE > '01-JAN-2001'
and PSD.PROJECTSTATUSCODE = 'BL'
group by to_date(to_char(psd.PROJECTSTATUSDATE, 'mm/dd/yyyy'),'mm/dd/yyyy')
order by to_date(to_char(psd.PROJECTSTATUSDATE, 'mm/dd/yyyy'),'mm/dd/yyyy')
Date Count
8/16/2013 102
8/19/2013 77
8/20/2013 257
8/21/2013 30
8/22/2013 173
8/23/2013 125
8/26/2013 41
8/27/2013 25
8/28/2013 14
