SQL select order by date in varchar

Question

I have my date in database in varchar column and i can't change it. However i want to sort things from newest to latest. My date in database looks like:

2014-09-22 10:28:28

So what i try is something like:

$sql = "SELECT * FROM axnmrs_cases WHERE vin = :vin ORDER BY STR_TO_DATE(date_created,'%b-%e-%Y') ASC LIMIT 30";

But unfortunately this not change anything for me , even if i change ASC to DESC , nothing changeing in result

and also something like:

$sql = "SELECT * FROM axnmrs_cases WHERE vin = :vin ORDER BY CONVERT(date_created, date, 103)";

This throw syntax SQL error and I have no idea why.

Is here anybody who can show me the right way?

Abhik Chakraborty · Accepted Answer · 2014-10-22 08:22:03Z

1

Date stored in varchar is not a real date and hence the order by also does not give you what you want. The best approach would be store date always in mysql native data types. However in your case you can use str_to_date() function to convert the varchar dates to real date and then use it for sort something as

order by str_to_date(date_created,'%Y-%m-%d %H:%i:%s');

answered Oct 22, 2014 at 8:22

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

6 Comments

Andurit Over a year ago

Hi , thanks for your answer. However it looks fine it's not working for me. This make no result (no error as well) $sql = "SELECT * FROM axnmrs_cases` WHERE vin = ':vin' ORDER BY STR_TO_DATE(date_created, '%Y-%m-%d %H:%i:%s') LIMIT 30";`

Andurit Over a year ago

is it change its because i have one SQL in another? .. I have something like: $stmt->execute(); while ($row = $stmt->fetch()){ ANOTHER SQL IS HERE }

Abhik Chakraborty Over a year ago

please provide the complete code in the question. Its hard to guess whats going on there but as far as ordering with varchar date is concern the above query should do it.

Andurit Over a year ago

Hi, you can find whole code here: pastebin.com/1a76XvFM but its pretty big, idk if it help :(

Abhik Chakraborty Over a year ago

Are you sure all the dates in the DB are saved as Y-m-d H:i:s format ?

|

Refilon · Accepted Answer · 2014-10-22 08:12:09Z

$sql = "SELECT * FROM `axnmrs_cases` WHERE `vin` = ':vin' ORDER BY `date_created` ASC LIMIT 30";

Already tried something like this?

GarethD · Accepted Answer · 2014-10-22 08:23:38Z

You are using the wrong format in your STR_TO_DATE function, if the date is in the format:

2014-09-22 10:28:28

Then you need to use

STR_TO_DATE(date_created, '%Y-%m-%d %H:%i:%s')

i.e. the format you give should match the format your varchar is in.

Example on SQL Fiddle

In your case you are using '%b-%e-%Y', so you are looking for a date like Jan-1-2014, for a full list of the specifiers in the format defintion see the My SQL Docs for DATE_FORMAT

Also, CONVERT(date_created, date, 103) does not work because it is SQL Server Syntax.

Finally, I would really, really try and change the column data type. Storing dates as a varchar is never a good idea. Anything else is just a workaround, not a solution.

Collectives™ on Stack Overflow

SQL select order by date in varchar

3 Answers 3

6 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Related