Postgresql query date format with sorting

Question

I'm trying with following query, it is giving me the correct date format but dates are not sorted correctly.

select to_char(date_of_service, 'mm/dd/yyyy') as service_date 
from cases 
order by service_date

Result I'm getting something like this (sample).

"01/01/2005"
"01/01/2010"
"01/02/2005"
"01/02/2010"

After date format I think it is getting converted into string and due to that sorting is not working. Any alternate solution available for this?

user330315 · Accepted Answer · 2023-01-10 06:31:09Z

1

You are sorting by the string value, not the real date value. So you need to change your ORDER BY clause:

select to_char(date_of_service, 'mm/dd/yyyy') as service_date 
from cases 
order by date_of_service;

answered Jan 10, 2023 at 6:31

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1