CODEWITHSUNDEEP
mysqlsqlsortingdatetime
jim smith
jim smith

Reputation: 2454

Mysql sort date format

I have an unavoidable situation where the dates are stored in UK date format, e.g.: 

31/12/2001  00:00:00

I need it in descending order, I've tried this but it errors 

select *, DATE_FORMAT(completiondate,'\%e/%c/%Y\') as cdate 
from projects 
where countries = 1 
order by cdate desc

Error:

check the manual that corresponds to your MySQL server version for the the right syntax to use near '' order by cdate desc'

I'm using MySQL 4.1.9

Upvotes: 0

Views: 3641

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272106

You are escaping the % character unnecessarily. But the actual problem is that that you have an un-terminated string literal in your query:

-- this does not terminate the string ----------v
select *, DATE_FORMAT(completiondate,'\%e/%c/%Y\') as cdate
from projects
where countries = 1
order by cdate desc

Change to:

SELECT *, DATE_FORMAT(completiondate,'%e/%c/%Y') AS cdate
FROM projects
WHERE countries = 1
ORDER BY cdate DESC

Upvotes: 1

Todd
Todd

Reputation: 581

Jim, your end solution was a huge help for me. My dates are in the 02/28/2013 format. I used the code:

SELECT *,str_to_date(SaleDate,'%m/%d/%Y') AS cdate FROM mytable ORDER BY cdate DESC

Thanks!

Upvotes: 1

jim smith
jim smith

Reputation: 2454

This was the end solution 

select *,completiondate from projects order by str_to_date(completiondate,'%d/%m/%Y %H:%i') desc

Upvotes: 2

smokestudley
smokestudley

Reputation: 101

SELECT * FROM projects WHERE countries = 1 order by cdate desc

I'm guessing you have cdate already in your database? If so, you don't need to set "date_format" since it's already there. But I may be wrong since I've never used 4.1.9

Upvotes: 0

Related Questions