How to get one day ahead of a given date?

by

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

Leave a Comment