Oracle order by date gives different results

Question

Just curious as to why the below two queries would give different results, when the testdate column is of datatype DATE.

select testdate from <table> order by to_date(testdate) desc;

Returns

18-DEC-14
17-DEC-14
14-JUL-14
10-JUL-14

.

select testdate from <table> order by testdate desc;

Returns

13-NOV-13
18-DEC-14
17-DEC-14
14-JUL-14

Why would that November 2013 result appear at the top of the second results when as I say the coumn TESTDATE is of data tyle DATE and to_date() resolves the issueX?

What does `select to_char(testdate, 'yyyy-mm-dd hh24:mi:ss') from order by testdate desc;` show you? — , Sep 07 '15 at 08:11
There is a tangential lesson in here, which is that when you are not sure why SQL returns a particular result, always look at the actual data. In this case, look at the values of to_date(testdate) verrrrry closely, and think about why they order in the way that they do. — David Aldridge, Sep 07 '15 at 08:16
Also: `to_date(testdate)` is totally pointless if `testdate` **really** is a date. That will convert the `date` to a `varchar` and then convert the `varchar` back to a `date` which it was to begin with. — , Sep 07 '15 at 08:18
[**Never use TO_DATE on a DATE**](http://stackoverflow.com/a/29559609/3989608) It will implicitly convert it into string and then back to date using locale-specific NLS format. — Lalit Kumar B, Sep 07 '15 at 08:23
select to_date(testdate, 'yyyy-mm-dd hh24:mi:ss') from order by testdate desc; returns "A non-numeric character was found where a numeric was expected" — KS1, Sep 07 '15 at 08:25
Read what has been already said, to_date on a date is pointless. `testdate` is a date not a string. — Lalit Kumar B, Sep 07 '15 at 08:31
I was answering a question from above from 'a_horse_with_no_name', and whilst I understand that to_date(testdate) is pointless in that it converts date>varchar>date, it is (for a reason I don't know) returning the correct result. Hence the original question, why are the two queries behaving differently. — KS1, Sep 07 '15 at 08:37
Again: check the result of the query in my first comment. I guess the first rows is actually `1913-11-13` but your SQL client hides this information by not showing the full year and the implicit data type conversion that is happening in the first query changes the year 1913 to 2013 — , Sep 07 '15 at 08:42
Congrats to the horse with no name! The following query [select to_char(testdate,'DD-MON-YYYY HH24:MI:SS') from table order by testdate desc;] reveals the date [13-November-3013]! Which is somewhat worrying as we may have several queries returning this as the latest date in some reports!!!! — KS1, Sep 07 '15 at 08:52
Time to investigate who/how a date from nearly a millennium away got into our database. I suspect the obvious answer is that this should be 2013. — KS1, Sep 07 '15 at 08:54
@KPS-UK Probably because your legacy system might still be using `YY` format while it was addressed long time bak as **Y2K** bug. — Lalit Kumar B, Sep 07 '15 at 09:41
That's one of the reasons I detest Oracle's choice for the default date format in SQL*Plus. I have configured all the SQL clients I use to use `yyyy-mm-dd` as the display format. — , Sep 07 '15 at 09:52
@a_horse_with_no_name It also helps to keep in mind to use ANSI date literal as and where possible `DATE 'YYYY-MM-DD'`. — Lalit Kumar B, Sep 07 '15 at 10:29
@KPS-UK, you need to learn the subtle but very important difference between "the correct result" and "the expected result". This little exercise proves that the expected result is not always the correct result! — Jeffrey Kemp, Sep 08 '15 at 03:20

score 2 · Answer 1 · edited May 23 '17 at 12:01

Based on OP's reply via comments, the issue is:

SQL> WITH dates(dt) AS(
  2  SELECT to_date('18-DEC-2014', 'DD-MON-RR') FROM dual UNION ALL
  3  SELECT to_date('17-DEC-2014', 'DD-MON-RR') FROM dual UNION ALL
  4  SELECT to_date('14-JUL-2014', 'DD-MON-RR') FROM dual UNION ALL
  5  SELECT to_date('10-JUL-2014', 'DD-MON-RR') FROM dual UNION ALL
  6  SELECT to_date('13-NOV-3013', 'DD-MON-RR') FROM dual
  7  )
  8  SELECT dt yy_date,
  9    TO_CHAR(dt, 'DD-MON-YYYY') yyyy_date
 10  FROM dates
 11  ORDER BY dt DESC;

YY_DATE   YYYY_DATE
--------- -----------
13-NOV-13 13-NOV-3013
18-DEC-14 18-DEC-2014
17-DEC-14 17-DEC-2014
14-JUL-14 14-JUL-2014
10-JUL-14 10-JUL-2014

SQL>

Th client is displaying the year as YY due to which year 3013 is displayed as 13 and along with other dates 2014, it looked as if 2013 is ordered before 2014 in descending order.

Use TO_CHAR to display the date in your desired format, and use TO_DATE to convert a literal into date.

On a side note,

Never use TO_DATE on a DATE, It will implicitly convert it into string and then back to date using locale-specific NLS format.

score 1 · Answer 2 · answered Sep 07 '15 at 08:36

1

TO_DATE function expects string to convert it to date with a given format. Providing date column to to_date may result in unexpected behaviour even oracle does not complain about the given input.

answered Sep 07 '15 at 08:36

Ozan

1,044
1
9
23

Oracle order by date gives different results

2 Answers2