Reputation: 11285

How to select oldest date from MySQL

Is there a specific way to select the oldest (or two oldest) dates from a column in MySQL?

I imagine I would use the ordered by criterion.

Upvotes: 27

Answers (4)

Reputation: 171

if the column type is date or timedate

SELECT * FROM tablename WHERE 1 ORDER BY datecolumn ASC LIMIT 2

if the column type is text or varchar

SELECT *,DATE(STR_TO_DATE(datecolumn , '%H:%i %d-%m-%Y' )) AS columnname  FROM tablename WHERE 1 ORDER BY columnname ASC LIMIT 2

You can choose your date format from here .

Upvotes: 1

Reputation: 360672

Single oldest is easy:

SELECT MIN(datefield) FROM yourtable

Oldest n values requires a LIMIT query:

SELECT datefield FROM yourtable ORDER By datefield ASC LIMIT n

Upvotes: 32

Reputation: 29870

You can order by the date field in your database. For oldest:

SELECT * FROM table WHERE condition ORDER BY dateField ASC LIMIT 1

For two oldest:

SELECT * FROM table WHERE condition ORDER BY dateField ASC LIMIT 2

etc, etc, ...

Upvotes: 47

Reputation: 3905

select MyDate from MyTable order by MyDate asc limit 2

Upvotes: 1