Datetime equal or greater than today in MySQL

Question

What's the best way to do following:

SELECT * FROM users WHERE created >= today;

Note: created is a datetime field.

Answer 1

SELECT * FROM users WHERE created >= CURDATE();

But I think you mean created < today

Answer 2

SELECT * FROM myTable WHERE  DATE(myDate) = DATE(NOW())

Read more: http://www.tomjepson.co.uk/tutorials/36/mysql-select-where-date-today.html

Answer 3

SELECT * FROM users WHERE created >= NOW();

if the column is datetime type.

Answer 4

Answer marked is misleading. The question stated is DateTime, but stated what was needed was just CURDATE().

The shortest and correct answer to this is:

SELECT * FROM users WHERE created >= CURRENT_TIMESTAMP;

Answer 5

If 'created' is datetime type

SELECT * FROM users WHERE created < DATE_ADD(CURDATE(), INTERVAL 1 DAY);

CURDATE() means also '2013-05-09 00:00:00'

Answer 6

If the column have index and a function is applied on the column then index doesn't work and full table scan occurs, causing really slow query.

Bad Query; This would ignore index on the column date_time

select * from users
where Date(date_time) > '2010-10-10'

To utilize index on column created of type datetime comparing with today/current date, the following method can be used.

Solution for OP:

select * from users
where created > CONCAT(CURDATE(), ' 23:59:59')

Sample to get data for today:

select * from users
where 
    created >= CONCAT(CURDATE(), ' 00:00:00') AND
    created <= CONCAT(CURDATE(), ' 23:59:59')

Or use BETWEEN for short

select * from users 
where created BETWEEN 
      CONCAT(CURDATE(), ' 00:00:00') AND CONCAT(CURDATE(), ' 23:59:59')

Tip: If you have to do a lot of calculation or queries on dates as well as time, then it's very useful to save date and time in separate columns. (Divide & Conquer)

Answer 7

SELECT * FROM users WHERE created >= now()

Answer 8

The below code worked for me.

declare @Today date

Set @Today=getdate() --date will equal today    

Select *

FROM table_name
WHERE created <= @Today

Answer 9

SELECT * FROM table_name WHERE CONCAT( SUBSTRING(json_date, 11, 4 ) ,  '-', SUBSTRING( json_date, 7, 2 ) ,  '-', SUBSTRING(json_date, 3, 2 ) ) >= NOW();

json_date ["05/11/2011"]

Answer 10

you can return all rows and than use php datediff function inside an if statement, although that will put extra load on the server.

if(dateDiff(date("Y/m/d"), $row['date']) <=0 ){    
}else{    
echo " info here";    
}