如何在Oracle中格式化和排序日期? [英] How to format and sort a date in Oracle?

问题描述

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

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.

我需要一些帮助才能在同一查询中实现两者.请帮助我.

I need some help to achieve both in the same query. Kindly help me on the same.

我使用的查询是

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

推荐答案

听起来您想要类似的东西

It sounds like you want something like

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

在SELECT列表中，您要返回一个字符串，该字符串代表您首选格式的日期.在ORDER BY子句中，您想按实际日期排序.例如，使用标准的EMP和DEPT表

In the SELECT list, you want to return a character string that represents the date in your preferred format. In the ORDER BY clause, you want to order by the actual date. Using the standard EMP and DEPT tables, for example

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

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 DISTINCT and before doing the 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.

这篇关于如何在Oracle中格式化和排序日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！