[SOLVED] MySQL Column Year Parsing and Sorting

Issue

I have one table, I want to sort by years. However, we have a problem.

The column is in text format. Because it is in the years before Christ.

First I need to list the years before Christ and then the present years.

Table: date_events

event_year event_title
B.C 405 example
B.C 406 example
2022 example

Solution

You can order by the column event_year, but replacing 'B.C ' by a minus sign '-', and converting it to a signed:

select *
from date_events
order by cast(replace(event_year, 'B.C ', '-') as signed);

Output:

event_year event_title
B.C 406 example
B.C 405 example
2022 example

Fiddle

Answered By – Zakaria

Answer Checked By – Gilberto Lyons (BugsFixing Admin)

Leave a Reply

Your email address will not be published.