MYSQL Select rows where date is older than datetime

Question

I am coding a status item for a website and need to load statuses older than a variable that contains a datetime.

So the database has a field called added date which is a datetime field and I have a string in datetime format and i want to select all rows older than my datetime format string.

How would this be structured in MYSQL?

Thanks in advance.

Everyone, PLEASE NOTE: the format is DATETIME.... – André Figueira May 12 '12 at 17:34 — André Figueira, May 12 '12 at 17:34

juergen d · Accepted Answer · 2012-05-12T17:46:39.450

38

 select status_column from your_table
 where added_date < '2012-05-12'

edited May 12 '12 at 17:46

answered May 12 '12 at 17:27

juergen d

201,996
37
293
362

Hi there, it's actually datetime format will it still work? i.e. 2012-05-12 14:23:23 – André Figueira May 12 '12 at 17:28
the OP says 'older than', shouldn't the `>` be a `<`? – Simon Forsberg May 12 '12 at 17:28
`>` for newer, **`<`** for older... :) – pete May 12 '12 at 17:28
@AndréFigueira: Yes it will work with `datetime` strings as well. – juergen d May 12 '12 at 17:46
@juergend Hi I just tested, it doesn't work with datetime.... only partially but misses out a lot of dates. – André Figueira May 12 '12 at 17:51
@AndréFigueira: Works for me. See [this example](http://sqlfiddle.com/#!2/5fa3e/1) – juergen d May 12 '12 at 17:54
@juergend using your method with the following query: SELECT * from status WHERE addedDate < '2012-01-01 00:00:00' Returns just one results, when it should be returning 4 – André Figueira May 12 '12 at 18:05
@AndréFigueira: That is odd. The SQLFiddle example works. Even after you added other years. Try `where date(added_date) < cast('2012-05-12 10:00:00' as date)` – juergen d May 12 '12 at 18:07

score 5 · Answer 2 · answered May 12 '12 at 17:47

5

mysql DATETIME type is used for values that contain both date and time parts.

Try this

SELECT datecolumn 
FROM table
WHERE datetime_date > '2012-05-12 01-32-56'

answered May 12 '12 at 17:47

Moyed Ansari

8,436
2
36
57

score 1 · Answer 3 · answered May 12 '12 at 17:33

1

try this

$today = date('Y-m-d');

 SELECT column_name FROM table_name WHERE year(added_date) > $today AND month(added_date) > $today

answered May 12 '12 at 17:33

M.I.T.

1,040
2
17
34

MYSQL Select rows where date is older than datetime

3 Answers3

Linked

Related