How to format and sort a date in Oracle?

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 achie开发者_如何学Pythonve 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

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

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.

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

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

You don't say what your application is written in, but in some environments (e.g. Oracle APEX, Oracle Reports) the solution is to not use to_char in the query, but then to apply the desired formatting in the tool's "column properties" or similar.

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 Date objects instead and use e.g. a SimpleDateFormat to do the formatting in Java (after sorting).

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.

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