Select one day back from database

I used BETWEEN because the date column is likely DATETIME (on MySQL & SQL Server, vs DATE on Oracle), which includes the time portion so equals means the value has to equal exactly. These queries give you the span of a day.

down voteaccepted

MySQL SELECT * FROM TABLE t WHERE t.date BETWEEN DATE_SUB('2010-07-29', INTERVAL 1 DAY) AND '2010-07-29'

SQL Server

SELECT *
  FROM TABLE t
 WHERE t.date BETWEEN DATEADD(dd, -1, '2010-07-29')
                  AND '2010-07-29'

Oracle

SELECT *
  FROM TABLE t
 WHERE t.date BETWEEN TO_DATE('2010-07-29', 'YYYY-MM-DD') - 1
                  AND TO_DATE('2010-07-29', 'YYYY-MM-DD')