Suppose I have a date 2010-07-29. Now I would like to check the result of one day ahead. how to do that
For example,
SELECT * from table where date = date("2010-07-29")
How to do one day before without changing the string “2010-07-29”?
I searched and get some suggestion from web and I tried
SELECT * from table where date = (date("2010-07-29") - 1 Day)
but failed.
Answer
MySQL
SELECT *
FROM TABLE t
WHERE t.date BETWEEN DATE_SUB('2010-07-29', INTERVAL 1 DAY)
AND '2010-07-29'
Change DATE_SUB to DATE_ADD if you want to add a day (and reverse the BETWEEN parameters).
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')
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.
Attribution
Source : Link , Question Author : skydoor , Answer Author : OMG Ponies