oracle - how to order by with date in the format dd-MMM-yy

Question

I am able to select records and all like in the query below but select statement is picking records randomly that were create after the specified date. I am curious if there is a way to order by this date in the format dd-MMM-yy like 05-MAY-21..

              select * from Employees 
              where createDt >= '05-MAY-21'
              order by CreateDt asc

This statement is not giving what I am hoping for, which is start with records from date 05, 06, etc..

thank you in advance.

What data type is the CreateDt column? Is it actually a `date` or a `timestamp` type? What output are you getting from the query exactly that is a problem? It looks like it should work fine if CreateDt is a `date` type. — pmdba, Aug 04 '21 at 20:24
and the order in which the records are weird.. like dates in the order of 08, 11, 12, 24, 24, 27, 27, 31, etc — challengeAccepted, Aug 04 '21 at 20:27
What's weird? They're still in ascending order, as requested... — pmdba, Aug 04 '21 at 20:30

MT0 · Answer 1 · 2021-08-04T20:45:47.873

3

date in the format dd-MMM-yy like 05-MAY-21

A DATE is a binary data-type and it does NOT have any format.

If you do:

select *
from   Employees 
where  createDt >= DATE '2021-05-05'
order by CreateDt asc

Then it will sort the dates in ascending order by year then by month and then by day and then by hour and then by minute and then by second (yes, a DATE always has a time component).

If you do:

select *
from   Employees 
where  createDt >= '05-MAY-21'
order by CreateDt asc

Then you are implicitly asking Oracle to convert the string to a DATE and your query is actually doing:

select *
from   Employees 
where  createDt >= TO_DATE(
                     '05-MAY-21',
                     ( SELECT value
                       FROM   NLS_SESSION_PARAMETERS
                       WHERE parameter = 'NLS_DATE_FORMAT' )
                   )
order by CreateDt asc

If the NLS_DATE_FORMAT session parameter does not match the format of your string then you will get unexpected results.

If you want to order the dates alphabetically, rather than chronologically then convert them to a string in the ORDER BY clause:

select *
from   Employees 
where  createDt >= DATE '2021-05-05'
order by TO_CHAR(CreateDt, 'DD-MON-YY') asc

However, I'm not sure hy you would want to do this.

edited Aug 04 '21 at 20:45

answered Aug 04 '21 at 20:40

MT0

143,790
11
59
117

If I use select * from Employees where PROCESS = 'REGISTER' and Create_dt >= '09-MAY-17' order by Request_id asc is giving me what I want.. But how do I limit that to send only 10 records? when I add Rownum <=10, its returning not in the same order like the query intially showed.. – challengeAccepted Aug 04 '21 at 22:34
@challengeAccepted - What version of Oracle are you using? If you want to use `rownum`, you'd need to nest the query with the `order by`. If you're using a recent version of Oracle, you can use `fetch first 10 rows only`. – Justin Cave Aug 04 '21 at 23:09
@challengeAccepted That is an entirely different question to the one that is asked and is covered by [these answers](https://stackoverflow.com/q/2498035/1509264). It is not working for you as the `ROWNUM` pseudo-column is generated before the `ORDER BY` is applied so you are getting 10 random rows and then ordering them by date; as JustinCave said, you need to order first and then wrap it in another sub-query and apply the `ROWNUM` filter there (as per the accepted answer in the question I linked to). – MT0 Aug 05 '21 at 08:14

score 1 · Answer 2 · answered Aug 04 '21 at 20:39

WHERE clause seems to be wrong.

You said that createDt column's datatype is DATE. So, why are you comparing it to a string? Use date literal or TO_DATE function, don't rely on implicit datatype conversion.

select * from Employees 
where createDt >= date '2021-05-05'  -- No! '05-MAY-21'
order by CreateDt asc

If, on the other hand, you only think that column's datatype is DATE but is - actually - VARCHAR2, then you'll have to "convert" it to a valid date value using TO_DATE function with appropriate date format mask, hoping that all values in that column have the same, valid format:

select * from Employees 
where to_date(createDt, 'dd-mmm-yy', 'nls_date_language = english') >= date '2021-05-05'
order by to_date(createDt, 'dd-mmm-yy', 'nls_date_language = english') asc

If none of above helps, please, post some sample data. That includes CREATE TABLE and INSERT INTO several sample rows.

Marcin Wroblewski · Answer 3 · 2021-08-05T10:28:35.253

It would really help if you showed what results you're getting and what are your desired results...

Anyway, I'm trying to guess basing on the question and your comments

Let's start with "... statement is picking records randomly ..." and "This statement is not giving what I am hoping for, which is start with records from date 05, 06, etc.." and later: "and the order in which the records are weird.. like dates in the order of 08, 11, 12, 24, 24, 27, 27, 31, etc "

OK, the order "08, 11, 12, 24, 24, 27, 27, 31, etc." does not seem random, does it? It is pretty much the order you requested - ascending. I'm guessing that all those dates are in may, so you really are getting what you requested for: records from Employees table with createDt greater or equal to 5th of may and sorted in ascending order.

But you are saying that the dates order seems weird to you, you were expecting 05, 06, ..., and you got 08, 11, 12, 24

Oh! What seems to be bothering you is the fact that you have some gaps between the dates and that the dates do not start with May 5th. Is that it? Well, then, simple answer is: those dates are simply not present in the Employees table.

You ask elsewhere ".. how do I limit that to send only 10 records?" Ok, so let me guess what you would like to get.

You would like to see 10 consecutive dates starting with may 5th and the records which were created for each date.

In that case you have to "generate" those dates and then join them with your Employees table taking into account that for some of the dates you will have no row - hence LEFT JOIN.

with dates as
 (select date '2021-05-05' + (level - 1) d from dual connect by level <= 10)
select e.* 
  from dates d 
  left join employees e 
    on e.createDt = d.d
order by d.d

or, if the createDt contains time component

with dates as
 (select date '2021-05-05' + (level - 1) d from dual connect by level <= 10)
select e.* 
  from dates d 
  left join employees e 
    on e.createDt >= d.d and e.createDt < d.d + 1
order by d.d, e.createDt

I'm not sure if this is it, though

Also: you may use the literal '05-MAY-21' but only if you are absolutely sure that your session uses this exact date format. It's safer to use the datetime literal or to_date function ( to_date('05-MAY-21', 'DD-MON-YY') )

oracle - how to order by with date in the format dd-MMM-yy

3 Answers3