CODEWITHSUNDEEP
sql
Weasel
Weasel

Reputation: 74

SQL ORDER BY not working with varchar date

I have a query which is not ordering as i spect, also I think is a very simple order by but somehow is not working.

SELECT * FROM
(
SELECT 
    'May 19 2020  9:07AM' AS D
UNION
SELECT
    'May 19 2020  9:04PM'
) AS A
ORDER BY A.D DESC

The spected behavior is that 'May 19 2020 9:04PM' should be in the first row, as this is oldest.

Upvotes: 1

Views: 236

Answers (1)

zealous
zealous

Reputation: 7503

You need to convert it to date() in order by. Here is the demo.

This solution should work in SQL Server, PostgreSQL and SQL Lite

SELECT * FROM
(
SELECT 
    'May 19 2020  9:07AM' AS D
UNION
SELECT
    'May 19 2020  9:04PM'
) AS A
ORDER BY date(D) DESC

Output:

| d                   |
| ------------------- |
| May 19 2020  9:04PM |
| May 19 2020  9:07AM |

If you are using MySQL then unix_timestamp(d) desc should do the trick.

Upvotes: 3

Related Questions