How to convert String into DateTime Format in MySql/PostgreSQL

Question

I have table having few number of columns wherein one column is for Execution number (exe_number) which stores the data in varchar(100).

select*from exedetails;
+------------+------------+------------+-------------+
| exe_number | pass_count | fail_count | error_count |
+------------+------------+------------+-------------+
| 02Aug_E1   |         98 |          9 |           0 |
| 31Jul_E1   |         94 |          8 |           1 |
| 30Jul_E2   |         76 |          9 |           3 |
| 01Aug_E2   |         98 |          7 |           0 |
| 02Aug_E2   |         76 |          8 |           2 |
| 30Jul_E1   |         98 |         12 |           9 |
| 31Jul_E2   |         91 |          6 |           1 |
| 01Aug_E1   |         67 |         14 |           2 |
+------------+------------+------------+-------------+
8 rows in set (0.00 sec)

describe exedetails;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| exe_number  | varchar(100) | YES  |     | NULL    |       |
| pass_count  | int          | YES  |     | NULL    |       |
| fail_count  | int          | YES  |     | NULL    |       |
| error_count | int          | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Here 30/31/01/02 are a date, Aug/Jul is a month and E1/E2 is Execution iteration (This is for my simplicity).

Lets consider present date is

'02 August 2022'

So I want to write a query in such a way that all records starting with

02Aug_

should be at the top of table. Means It should get arranged according to date so that present day record should be at the top.

Query I wrote for this is not giving desired result:

select exe_number from exedetails order by exe_number desc;
+------------+
| exe_number |
+------------+
| 31Jul_E2   |
| 31Jul_E1   |
| 30Jul_E2   |
| 30Jul_E1   |
| 02Aug_E2   |
| 02Aug_E1   |
| 01Aug_E2   |
| 01Aug_E1   |
+------------+
8 rows in set (0.00 sec)

select exe_number from exedetails order by exe_number asc;
+------------+
| exe_number |
+------------+
| 01Aug_E1   |
| 01Aug_E2   |
| 02Aug_E1   |
| 02Aug_E2   |
| 30Jul_E1   |
| 30Jul_E2   |
| 31Jul_E1   |
| 31Jul_E2   |
+------------+
8 rows in set (0.00 sec)

Expected Result :

+------------+
| exe_number |
+------------+
| 02Aug_E1   |
| 02Aug_E2   |
| 01Aug_E1   |
| 01Aug_E2   |
| 31Jul_E1   |
| 31Jul_E2   |
| 30Jul_E1   |
| 30Jul_E2   |
+------------+

Is there any way in MySQL/PostgreSQL by which I can get my desired solution?

Like order should be like 22Aug_E1 then 22Aug_E2 then 21Aug_E1 then 21Aug_E2 then 20Aug_E1 then 20Aug_E2 — The Tester
– The Tester, Commented Aug 23, 2022 at 12:40
Does shown sample data is the value of the column in one row, or these are values from 6 separate rows? — Akina
– Akina, Commented Aug 23, 2022 at 13:18
But it's a single value - how can you have multiple rows sorted with a single value? Do you also want to split the elements of each column into multiple rows? — user330315
– user330315, Commented Aug 23, 2022 at 21:19
these sample data belongs to one column only To avoid the ambiguity provide this sample data as CREATE TABLE + INSERT INTO scripts please. Also provide desired output for this data (as textual table). And specify one definite DBMS, including its precise version. — Akina
– Akina, Commented Aug 24, 2022 at 7:20

score 1 · Accepted Answer · 2022-08-24 11:22:21Z

1

Just convert the "date part" to a proper date value, then you can sort by it:

select *
from exedetails
order by to_date(left(exe_number, 5), 'ddmon') desc, 
         right(exe_number, 2)

Note that to_date() working with a month name depends on your locale settings and the values in the column.

Online example for Postgres

edited Aug 24, 2022 at 11:22

answered Aug 24, 2022 at 11:16

user330315

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

2 Comments

The Tester Over a year ago

Thank you so much...it is working Is it possible to arrange E1/E2/E3 along with date? because they are not arranged properly

user330315 Over a year ago

@TheTester they are included in the order by so I don't know what you mean with "arrange".

Collectives™ on Stack Overflow

How to convert String into DateTime Format in MySql/PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related