Get max date in PHP

Question

How do I get the 'max' date from the following table using MySQL?

date_time
-----------------------------
Wednesday, 21 July 2010 20:41:51
Tuesday, 19 October 2010 16:7:41 
Tuesday, 29 November 2010 16:7:41

I want to retrieve the maximum date from the table.

Possible duplicate: *[How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231)*. — Peter Mortensen, Jun 11 '11 at 13:10

score 5 · Accepted Answer · answered Feb 16 '11 at 08:55

mysql> select str_to_date('Wednesday, 21 July 2010 20:41:51', '%W, %d %M %Y %T');
+--------------------------------------------------------------------+
| str_to_date('Wednesday, 21 July 2010 20:41:51', '%W, %d %M %Y %T') |
+--------------------------------------------------------------------+
| 2010-07-21 20:41:51                                                |
+--------------------------------------------------------------------+

str_to_date

Don't reinvent the wheel, stored the column in date-time

alter table your_table add column new_date_time datetime;
update your_table set new_date_time=str_to_date(date_time, '%W, %d %M %Y %T');
alter table channge column date_time ditch_date_time varchar(255);
alter table channge column new_date_time date_time datetime;

To get max value

select max(date_time) from your_table;

If you like to keep varchar (good luck with that)

select max(str_to_date(date_time, '%W, %d %M %Y %T')) from your_table;

oh.. thank u very much. I kept the field in varchar format only, it's working. — vishnu, Feb 16 '11 at 09:08

score 1 · Answer 2 · answered Feb 16 '11 at 08:26

1

If running max on that format doesn't work I guess you could try to reformat it in the query to a format that will allow max using date format. As long as the column is corretly formated you should be able to just do an order by desc though.

answered Feb 16 '11 at 08:26

Sondre

1,878
18
33

score 1 · Answer 3 · answered Feb 16 '11 at 08:30

1

SELECT * FROM date_time ORDER BY STR_TO_DATE(date,'%Y %M,%d') DESC LIMIT 1

answered Feb 16 '11 at 08:30

Shakti Singh

84,385
21
134
153

That would be my next answer then. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date – samy Feb 16 '11 at 08:44

score 1 · Answer 4 · answered Feb 16 '11 at 08:31

1

The only possible solution is to store the date in the proper format.
So, change your field type to datetime

answered Feb 16 '11 at 08:31

Your Common Sense

156,878
40
214
345

I wouldn't say its the only possible solution, but it is the best solution. – seanieb Feb 16 '11 at 08:38
@seanieb when you will have enough practical experience, you will learn that not to follow lame misconcepts coming from laziness and ignorance, and to correct these issues as soon as possible, is the only solution. – Your Common Sense Feb 16 '11 at 09:08

score 0 · Answer 5 · answered Feb 16 '11 at 08:23

0

SELECT date_time FROM TABLE ORDER BY date_time DESC LIMIT 1

This will work when your date_time field is of the type DATETIME or whatever other date specific type there is.

answered Feb 16 '11 at 08:23

samy

324
2
7

Actually the date_time field type is varchar – vishnu Feb 16 '11 at 08:27

score 0 · Answer 6 · answered Feb 16 '11 at 08:27

0

You can use

SELECT mydate
FROM mytable
ORDER BY mydate DESC
LIMIT 1

Or just

Select max(mydate) from mytable

answered Feb 16 '11 at 08:27

quocnguyen

192
2
10

i never store date in database using varchar. why someone do that ? – quocnguyen Feb 16 '11 at 09:01
Up-voting this as it's the best way to get the max date/datetime. You should never store it in varchar format anyway. – Younes Nj Oct 24 '17 at 01:10

Get max date in PHP

6 Answers6