Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL selecting records with dates before today

Tags:

sql

php

mysql

I have a mysql DB with tables, of which in the one table I have a date type field, I want the most recently passed date - so I want it to order by dates descending, but only take records from before today, and then take only the top most one using the LIMIT function, and also there is the addition of the WHERE clause being that the offer must be for the selected city.

$result = mysql_query("
SELECT * FROM offers 
WHERE city = ".$_SESSION["city"]." 
ORDER BY exp_date DESC 
LIMIT 0, 1");   
like image 594
David Avatar asked Feb 20 '12 09:02

David


People also ask

How do I select a date before today in SQL?

Discussion: To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime ) and cast it to date . In SQL Server, you can subtract or add any number of days using the DATEADD() function.

How do I select a specific date range in SQL?

SELECT * FROM YourTable. WHERE [dateColumn] >DATEADD(day,1,'4/25/2022') AND [dateColumn] <= DATEADD(day,1,'4/26/2022') AND DATEPART(hh,[dateColumn]) >= 7 AND DATEPART(hh,[dateColumn]) <= 19.

How do I query today's date in SQL?

To get the current date and time in SQL Server, use the GETDATE() function. This function returns a datetime data type; in other words, it contains both the date and the time, e.g. 2019-08-20 10:22:34 . (Note: This function doesn't take any arguments, so you don't have to put anything in the brackets.)


2 Answers

ADD another condition to where clause

$result = mysql_query("
SELECT * FROM offers 
WHERE city = ".$_SESSION["city"]."  and Date < CURRENT_DATE()
ORDER BY exp_date DESC 
LIMIT 1");
like image 180
Haim Evgi Avatar answered Oct 14 '22 05:10

Haim Evgi


SELECT * FROM deals WHERE city = 2 AND exp_date < CURDATE() 
ORDER BY exp_date DESC LIMIT 0, 1
like image 35
David Avatar answered Oct 14 '22 06:10

David