How to ASC DESC Date which data type is varchar

Question

How do can I asc date? I have the folllowing in sql

2014-3-18
2014-1-15
2014-3-20
2015-3-18

I tried the ff codes but it just gives me error in my db. And yes the data type of the column is varchar. And also there's NULL values in that column.

SELECT * 
FROM `locate` 
ORDER BY 
   CONVERT(DateTime, `Date`,101)  DESC

Please help. Thanks! :)

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`Date`,101) DESC LIMIT 0, 25' at line 4 — Culascage, Apr 24 '17 at 07:01
remove `` while selecting the database name and also while converting. — Ria Sen, Apr 24 '17 at 07:01
xampp is not a database. Your query Looks like mssql i guess you use mysql — Jens, Apr 24 '17 at 07:01
Mysql based `CONVERT` takes 2 parameters at max, why do you have the third one there? — Hanky Panky, Apr 24 '17 at 07:02
if you are using mysql, use `str_to_date(your_date_column, '%Y/%m/%d')` — marmeladze, Apr 24 '17 at 07:03
@MonPadi Add the error message to your question using the EDIT function. Not as a comment — Jens, Apr 24 '17 at 07:07
**(1)** You mention that you're getting an error but cannot be bothered to state the _error message_. Understanding _error messages_ is half the problem solved - it's ***not*** some sort of "state secret"! **(2)** Don't store dates as strings, and you won't have to deal with convertion and the resultant errors. **(3)** If you do store as strings, use "yyyy-mm-dd" (NB Month and day padded to 2 digits; year to 4.) Then the string ordering and date ordering will be identical - again avoiding the need for any error prone conversion. — Disillusioned, Apr 24 '17 at 07:13
Possible duplicate of [how to convert a string to date in mysql?](http://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) — Disillusioned, Apr 24 '17 at 07:15
Yep possible duplicate. But that article doesn't have the ORDER BY so in case there are dummies(like me) search this kind of query. This can helpful. — Culascage, Apr 24 '17 at 07:19

Jens · Accepted Answer · 2017-04-24T07:09:57.577

1

Looks like you use mysql so you can not use the mssql convert function. you have to use STR_TO_DATE

SELECT * 
FROM `locate` 
ORDER BY 
   STR_TO_DATE(`Date`,'%Y-%m-%d')  DESC

For more Information see the documentation

edited Apr 24 '17 at 07:09

answered Apr 24 '17 at 07:03

Jens

67,715
15
98
113

Thank you! I need some basics between mssql and mysql. – Culascage Apr 24 '17 at 07:07

Ria Sen · Answer 2 · 2017-04-24T07:18:10.483

0

Use the order by this way :

SELECT DATE_FORMAT(`date`, '%d-%m-%Y') as date_formatted
FROM locate 
ORDER BY `datetime` DESC

Please let me know if it works.

edited Apr 24 '17 at 07:18

answered Apr 24 '17 at 07:03

Ria Sen

94
13

Lets hear from the author if it works or not. If not we can find an alternative. – Ria Sen Apr 24 '17 at 07:05
ofcourse will not work -)) because s/he uses xampp - which is a apache, mysql, php/perl/python bundle. – marmeladze Apr 24 '17 at 07:07
That works too. But it does not show the other column's values. It just shows the date. I already found the solution below. – Culascage Apr 24 '17 at 07:13
only date is selected so it shows that. You can select other columns by adding " , " . Hope this helps. Please upvote if it helped. – Ria Sen Apr 24 '17 at 07:17
that can be another way to handle this use case – Ria Sen Apr 24 '17 at 07:27

score 0 · Answer 3 · answered Apr 24 '17 at 07:10

I'm guessing EventDate is a char or varchar and not a date otherwise your order by clause would be fine.

You can use CONVERT to change the values to a date and sort by that

SELECT * 
FROM 
     vw_view 
ORDER BY 
   CONVERT(DateTime, EventDate,101)  DESC

The problem with that is, as Sparky points out in the comments, if EventDate has a value that can't be converted to a date the query won't execute.

This means you should either exclude the bad rows or let the bad rows go to the bottom of the results

To exclude the bad rows just add WHERE IsDate(EventDate) = 1

To let let the bad dates go to the bottom you need to use CASE

e.g.

ORDER BY 
    CASE
       WHEN IsDate(EventDate) = 1 THEN CONVERT(DateTime, EventDate,101)
       ELSE null
    END DESC

How to ASC DESC Date which data type is varchar

3 Answers3