How to get one day ahead of a given date?

Question

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 1

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.

Answer 2

If you're using Oracle, you can use the + and - operators to add a number of days to a date.

http://psoug.org/reference/date_func.html

Example:

SELECT SYSDATE  +  1 FROM dual;

Will yield tomorrow's date.

If you're not using Oracle, please tell use what you ARE using so we can give better answers. This sort of thing depends on the database you are using. It will NOT be the same across different databases.

Answer 3

Depends of the DateTime Functions available on the RDBMS

For Mysql you can try:

mysql> SELECT DATE_ADD('1997-12-31',
->                 INTERVAL 1 DAY);


mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
    -> '1997-12-02'