How do I sort by Date correctly?

Question

I'm trying to sort by date in one of the columns but with the code I do, it comes not in order. For example, if I order by descending date, it will come up as 31st October 2022 then the next couple would be 31st May 2024 then 31st May 2023, 31 March 2023, 31st July 2023, 31st January 2023, 31st December 2022. I need to be able to sort by deals which will have a close date nearer the time.

The code is:

select D.ROWID,
    '<b style="font-size: large; color:#DC4300;">' || D.CUSTOMER || '</b><br>' ||
    '<b>Project Type: </b>' || D.PROJECT || '<br>' ||
    '<b>Engagement Type: </b>' || NVL(D.ENGAGEMENT, 'Not Specified') || '<br>' ||
    '<b>Opportunity ID: </b>' || NVL(D.OPP_ID, 'Not Specified') || '<br>' ||
    --'<b>SF Rep: </b>' || com.COMMERCIAL_MANAGER || '<br>' ||
    '<b>CPR: </b>' || D.CPR || '<br>' ||
    '<b>VP: </b>' || NVL(D.VP, 'N/A') || '<br>' ||
    '<b>Country: </b>' || c.COUNTRY as ENGAGEMENT,
    D.CUSTOMER,
    com.COMMERCIAL_MANAGER,
    D.ARR,
    case D.LICENSE
        when null then 0
        else D.LICENSE
    end as LICENSE,
    D.SALES_STAGE,
    NVL(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') as CLOSING_DATE,
    '<b>Deal Summary: </b>' || D.COMPLEX_DEMANDS || '<br>' ||
    '<b>Approval Date: </b>' || NVL(to_char(D.APPROVALS_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br>' ||
    '<b>Drafting Date: </b>' || NVL(to_char(D.DRAFTING_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br><br>' ||
    --'<b style="color: #DC4300;">Closing Date: </b>' || NVL(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br><br>' ||
    '<b>Customer Facing: </b>' || NVL(D.CUSTOMER_FACING, 'Not Specified') || '<br>' ||
    '<b>Tier 3 & 1 Engaged: </b>' || D.TIER3_1 || '<br>' ||
    '<b>Closure Risk(s): </b>' || NVL(D.CLOSURE_RISK, 'None') || '<br><br>' ||
    '<b>Challenge(s): </b>' || NVL(D.CHALLENGES, 'None') as DEAL_DETAILS,
    NVL(D.FBE, 'N/A'),
    D.NEXT_STEPS || '<br>' || 
    '<b>Contract Standpoint: </b>' || NVL(D.CONTRACT_STANDPOINT, 'N/A')|| '<br>' ||
    '<b>Main Contact: </b>' || D.CUSTOMER_CONTACT_NAME || ' - ' || D.CUSTOMER_CONTACT_ROLE as NEXT_STEPS
   
from TECHCOM D, 
    COUNTRY_LOOKUP c, 
    COMMERCIAL_MANAGER_LOOKUP com
where D.country_ID = c.country_ID
    and D.COMMERCIAL_MANAGER_ID = com.COMMERCIAL_MANAGER_ID
    and D.ARR is not null
    and not(D.SALES_STAGE in ('Won', 'Lost'))
ORDER BY D.ARR desc

Post your `CREATE TABLE` statement. What is the **exact data-type** of your "date" columns? And **don't use the legacy, horrible, evil comma JOIN syntax**: use SQL-92 joins. — Dai, Oct 03 '22 at 10:03
It looks like you are ordering the string-representation of those dates in descending order. You need to order by the [real `date`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-7690645A-0EE3-46CA-90DE-C96DF5A01F8F) value — Hans Keﬆing, Oct 03 '22 at 10:10
@HansKeﬆing Could you help me with how I could do that please? — , Oct 03 '22 at 10:12
@Dai Could you explain what you mean please? There is no CREATE TABLE statement. If there is, I cannot find it as it is a table I have been assigned to work on and wasn't one which I created. — , Oct 03 '22 at 10:14
See here for instructions on how to get the `CREATE TABLE` statement: https://stackoverflow.com/a/18264638/159145 — Dai, Oct 03 '22 at 10:25
As for the `JOIN`, see here: https://stackoverflow.com/a/4001079/159145 — Dai, Oct 03 '22 at 10:26
Don't return HTML code in SQL; instead return the data and then use Apex to output the formatting. Don't use `style` attributes to format HTML; instead, use CSS and semantic markup. Don't sort by strings; instead, sort by the underlying dates. — MT0, Oct 03 '22 at 12:57
@MT0 I did not create this code. This was already in place before I got assigned to this project — , Oct 03 '22 at 13:34
That does not man that you should not take this opportunity to improve/refactor it and follow best practice. — MT0, Oct 03 '22 at 13:45

score 1 · Answer 1 · answered Oct 03 '22 at 10:31

oracle-apex tag suggests you use Oracle database.

D.ARR is that column, isn't it? Looks like its datatype is VARCHAR2 so data is sorted as strings, not dates. That's most usually a bad idea - dates should be stored into DATE datatype columns.

Anyway: if these values are as you said, then convert them to DATEs first (using to_date function with appropriate format model), and then sort them. Unfortunately, the th format model can be used only for output, so you'll have to use substrings.

Something like this:

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> with test (name, arr) as
  2    (select 'Little' , '31st January 2024'  from dual union all
  3     select 'Foot'   , '13th July 2026'     from dual union all
  4     select 'Redders', '2nd September 2022' from dual
  5    )
  6  select name,
  7    arr,
  8    to_date(
  9      regexp_substr(arr, '\d+', 1, 1)          ||' '||       --> day
 10      regexp_substr(arr, '[[:alpha:]]+', 1, 2) ||' '||       --> month name
 11      regexp_substr(arr, '\d+$')                             --> year
 12      , 'dd Month yyyy') arr_date
 13  from test
 14  order by arr_date;

NAME    ARR                ARR_DATE
------- ------------------ ----------
Redders 2nd September 2022 02.09.2022
Little  31st January 2024  31.01.2024
Foot    13th July 2026     13.07.2026

SQL>

Once again: if you stored dates as dates, you'd simply order by arr. If you have a large data set, performance might suffer because of what you have to do with these values.

Sorry, it was sorted by D.ARR for another reason. But ideally, want to have it as CLOSING_DATE. Would it still work if replaced? — , Oct 03 '22 at 10:34
Column name is irrelevant; it is about the *principle*, so - yes, if you used CLOSING_DATE, it would still work. — Littlefoot, Oct 03 '22 at 10:35

score 1 · Answer 2 · answered Oct 03 '22 at 13:57

Use the underlying D.close_date column, which already has a DATE data-type (rather than using a string formatted date such as using the CLOSING_DATE alias in the ordering):

select D.ROWID,
       D.CUSTOMER,
       D.PROJECT as project_type,
       COALESCE(D.ENGAGEMENT, 'Not Specified') AS engagement_type,
       COALESCE(D.OPP_ID, 'Not Specified') AS Opportunity_ID,
       D.CPR,
       COALESCE(D.VP, 'N/A') AS vp,
       c.COUNTRY,
       D.CUSTOMER,
       com.COMMERCIAL_MANAGER,
       D.ARR,
       COALESCE(D.LICENSE, 0) as LICENSE,
       D.SALES_STAGE,
       COALESCE(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') as CLOSING_DATE,
       D.COMPLEX_DEMANDS As deal_demands,
       COALESCE(to_char(D.APPROVALS_DATE, 'ddth Month YYYY'), 'Not Specified') AS approval_date,
       COALESCE(to_char(D.DRAFTING_DATE, 'ddth Month YYYY'), 'Not Specified') AS Drafting_Date,
       COALESCE(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') AS closing_date,
       COALESCE(D.CUSTOMER_FACING, 'Not Specified') AS Customer_Facing,
       D.TIER3_1,
       COALESCE(D.CLOSURE_RISK, 'None') AS closure_risk,
       COALESCE(D.CHALLENGES, 'None') as Challenges,
       COALESCE(D.FBE, 'N/A') AS fbe,
       D.NEXT_STEPS AS next_steps,
       COALESCE(D.CONTRACT_STANDPOINT, 'N/A') As Contract_Standpoint,
       D.CUSTOMER_CONTACT_NAME AS Main_Contact,
       D.CUSTOMER_CONTACT_ROLE
from   TECHCOM D
       INNER JOIN COUNTRY_LOOKUP c
       ON (D.country_ID = c.country_ID)
       INNER JOIN COMMERCIAL_MANAGER_LOOKUP com
       ON (D.COMMERCIAL_MANAGER_ID = com.COMMERCIAL_MANAGER_ID)
where  D.ARR is not null
and    D.SALES_STAGE NOT IN ('Won', 'Lost')
ORDER BY D.close_date desc

Then format the page using Apex (rather than embedding the HTML in the SQL code) and use semantic markup and CSS to style the page (rather than HTML markup and inline styles).

How do I sort by Date correctly?

2 Answers2