Datetime equal or greater than today in MySQL
MysqlSqlDatetimeMysql Problem Overview
What's the best way to do following:
SELECT * FROM users WHERE created >= today;
Note: created is a datetime field.
Mysql Solutions
Solution 1 - Mysql
SELECT * FROM users WHERE created >= CURDATE();
But I think you mean created < today
You can compare datetime with date, for example: SELECT NOW() < CURDATE() gives 0, SELECT NOW() = CURDATE() gives 1.
Solution 2 - Mysql
SELECT * FROM myTable WHERE DATE(myDate) = DATE(NOW())
Read more: http://www.tomjepson.co.uk/tutorials/36/mysql-select-where-date-today.html
Solution 3 - Mysql
SELECT * FROM users WHERE created >= NOW();
if the column is datetime type.
Solution 4 - Mysql
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;
Solution 5 - Mysql
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'
Solution 6 - Mysql
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)
Solution 7 - Mysql
SELECT * FROM users WHERE created >= now()
Solution 8 - Mysql
The below code worked for me.
declare @Today date
Set @Today=getdate() --date will equal today
Select *
FROM table_name
WHERE created <= @Today
Solution 9 - Mysql
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"]
Solution 10 - Mysql
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";
}