date wise sort in sql

Question

example:

sh_date
30.11.2017
01.01.2018
31.12.2017

i want like this:

sh_date
30.11.2017
31.12.2017
01.01.2018

structure: varchar – Smritimay Debnath Jan 02 '18 at 11:27 — Smritimay Debnath, Jan 02 '18 at 11:27
Switch to `date` type instead. – jarlh Jan 02 '18 at 11:30 — jarlh, Jan 02 '18 at 11:30

Bhargav Chudasama · Answer 1 · 2018-01-02T11:35:00.350

0

try this one

select sh_date from table_name order by sh_date ASC

sh_date is a char or varchar and not a date otherwise your order by clause would be fine.

SELECT sh_date  
FROM 
     table_name
ORDER BY 
   CONVERT(sh_date , EventDate,101)  ASC

edited Jan 02 '18 at 11:35

answered Jan 02 '18 at 11:28

Bhargav Chudasama

Md. Tazbir Ur Rahman Bhuiyan · Answer 2 · 2018-01-02T11:35:32.157

0

you will have to convert it to datetime and then you can order:

select sh_date
from table_name
order by convert(datetime, sh_date, 103) ASC

for further study : please go to this thread Order by date (varchar)?

edited Jan 02 '18 at 11:35

answered Jan 02 '18 at 11:29

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`sh_date`, 103) ASC LIMIT 0, 25' at line 3 – Smritimay Debnath Jan 02 '18 at 11:31
select [sh_date] from table_name order by cast([sh_date] as datetime) asc – Md. Tazbir Ur Rahman Bhuiyan Jan 02 '18 at 11:33
edited my question. gave a link in the below part.. hope it will help you to figure out.. – Md. Tazbir Ur Rahman Bhuiyan Jan 02 '18 at 11:36

score 0 · Accepted Answer · answered Jan 02 '18 at 11:39

As the date is varchar

you can try bellow one in mysql

SELECT sh_date
FROM table_name
ORDER BY STR_TO_DATE(REPLACE(sh_date ,'.',',') ,'%d,%m,%Y') ASC

Need to convert string to date before sorting

For eg:30.11.2017 it will convert to date
SELECT STR_TO_DATE(REPLACE('30.11.2017' ,'.',',') ,'%d,%m,%Y')

Output
2017-11-30

please add if anything is missing

3 Answers3