How to format and sort date in Oracle?

Question

In my application, I am trying to format and sort the date, I use the to_char() function to format the date to my required format, but when I sort them, it sorts it as sorting strings. But I want them to be sorted by date.

I need help to achieve both requests. Please help me on the same.

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

Answer 1

It looks 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 the preferred format. In the ORDER BY you want to order the actual date. Using 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 added DISTINCT, the problem is that Oracle does not know that the function you are using (in this case TO_CHAR) provides a one-to-one comparison of the data in the table with the output data, for example, two different dates (October 1, 2010 10:15 : October 15 and October 1, 2010 11:45:50 PM) can generate the same character output, forcing Oracle to exclude one of the two lines "101-10-2010" but the two dates will be sorted differently. You can fix this problem by nesting your query and converting the string back to a date after executing DISTINCT and before executing 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. 

Answer 2

 SELECT to_char( your_date_column, your_format_mask ) as formate_purpose, FROM your_table ORDER BY to_date (formate_purpose) 

Try the code above

Answer 3

You do not say what is written in your application, but in some environments (for example, Oracle APEX, Oracle Reports) the solution does not use to_char in the query, but then apply the desired formatting in the Column Properties tool or the like.

Answer 4

The easiest way is to get the same request field again and sort based on the registered

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 

Answer 5

If you give Oracle a sort (recommended), just do it as described in Justin's answer. If for some reason you are sorting in Java, do not use to_char ; get dates as Date objects and use for example. a SimpleDateFormat for formatting in Java (after sorting).

Answer 6

For sqlplus, use alter session set nls_date_format for what you want the date format to be, and then just use the column name in the select statement and sort order.

Answer 7

I needed to group “by” and “by date”, but the “Date” field included “Time”, and I did not want to include time in grouping and sorting. So I converted the date to a character and then converted the character to Date to exclude time and sort by date, not text. This grouped the data by date and sorted by 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