select a date that is lesser than today

Question

I have a table like below.

id  date            value
12  2017-12-11      250
13  2017-12-11      256
14  2017-11-10      300
15  2017-11-09      400

I need to write a query where I can get the prior date of today. Like if today is 2017-12-11, the query should only return

id  date        value
14  2017-11-10  300

Please help me how can I get this.

Answer 1

because the initial question was tagged with sql-servermysqlpostgresql i provided 3 versions of solution

SQL Server version

You can use Convert(date, getdate()) to get today date (without time), and with a simple query you can get what you are looking for

SELECT TOP 1 *
From MyTable 
Where [date] < Convert(date, getdate()) 
ORDER BY [date] desc

SQLFiddle demo

PostgreSQL version

SELECT  *
From MyTable 
Where date < current_date
ORDER BY date desc
limit 1

SQLFiddle demo

MySQL version

SELECT  *
From MyTable 
Where date < current_date
ORDER BY date desc
limit 1

SQLFiddle demo

Answer 2

select * from yourtable t where t.date_value < getdate()