如何在Oracle中格式化和排序日期？

Question

在我的应用程序中，我正在尝试格式化和排序日期，我使用to_char()函数将日期格式化为所需格式，但当我对它们进行排序时，它按字符串排序。但我希望它们按日期排序。

我需要在同一查询中实现这两个目标。请帮我解决这个问题。

我使用的查询是：

SELECT to_char( t1.your_date_column1, your_format_mask ) as alias,
  FROM your_table1 t1,your_table2
 ORDER BY t1.your_date_column1

- Hariharbalaji

---

您的应用程序使用哪种编程语言或框架？ - Erich Kitzmueller

我正在使用Oracle 10g，应用程序是一个Java/J2EE应用程序。 - Hariharbalaji

Answer 1

听起来你想要的是类似于

SELECT to_char( your_date_column, your_format_mask )
  FROM your_table
 ORDER BY your_date_column

在 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.

如果你添加了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.

- Justin Cave

---

Justin：我尝试了你提到的相同方法，但是出现了错误。错误信息是ORA-01791“不是一个已选择的表达式”。 - Hariharbalaji

@Hari - 我更新了一个查询工作示例。你能发一下实际使用的查询，看看是不是会产生这个错误吗？ - Justin Cave

@ Justin: 当我尝试在单个表中执行你告诉我的方法时，它运行正常。但是当我尝试连接两个表时，它出现问题。 - Hariharbalaji

@Hari - 我修改了我的示例以连接两个表，所以它可以正常工作。你能否发布一个测试用例来重现问题（即CREATE TABLE语句、DML以插入一些样本行和生成异常的查询）？ - Justin Cave

抱歉回复晚了，我找到了问题所在。在select语句中，我加了distinct，当我去掉它后，它就按预期工作了。但是我不明白为什么在select子句中加入distinct会导致错误。 - Hariharbalaji

@Hari - 添加了有关“DISTINCT”的讨论。 - Justin Cave

Answer 2

SELECT 
         to_char( your_date_column, your_format_mask ) as formate_purpose,

FROM your_table

ORDER BY to_date (formate_purpose)

请尝试以上代码。

- Steve

Answer 3

最简单的方法是再次使用查询检索相同的字段，并基于该字段进行排序。

在您的示例中：

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

- Hardik Bhalani

Answer 4

您没有提到您的应用程序是用什么语言编写的，但在某些环境中（例如Oracle APEX，Oracle Reports），解决方案是在查询中不要使用to_char函数，而是在工具的“列属性”或类似设置中应用所需的格式。

- Tony Andrews

Answer 5

如果您让Oracle进行排序（建议使用），请像Justin Cave的答案所述那样进行操作。 如果由于某种原因您要在Java中排序，请勿使用to_char;而是将日期作为Date对象获取，然后在Java中使用SimpleDateFormat进行格式化（在排序之后）。

- Erich Kitzmueller

---

之前我曾经用SimpleDateFormat格式化过类似的东西，但是如果我有大量数据，比如75条记录，我就必须在循环中执行相同的操作。所以我想在查询级别上进行格式化。 - Hariharbalaji

Answer 6

对于sqlplus，使用alter session set nls_date_format来设置日期格式，然后在select语句中使用列名和排序顺序即可。

- Chris Panagopoulos

Answer 7

我想按日期字段分组和排序，但是日期字段包含了时间，我不想在分组和排序中包含时间。因此，我将日期转换为字符型，然后再将字符型转换回日期型，以消除时间并按日期而不是文本进行排序。这样就可以按日期分组并按日期排序。

-- 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

- dave mc

---

trunc函数可以在不需要任何转换的情况下删除时间。此外，在谓词中有一个隐式日期格式；date '2001-01-01' 可以在任何地方使用，但是 01-JAN-2001 只有在客户端程序具有特定日期格式和语言时才能使用。 - Jon Heller