tags:

views:

71

answers:

4

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.

A: 

If youre using MSSQL, you're looking for DateAdd() I'm a little fuzzy on the syntax, but its something like:

Select * //not really, call out your columns
From [table]
Where date = DateAdd(dd, -1, "2010-07-29",)

Edit: This syntax should be correct: it has been updated in response to a comment.
I may have the specific parameters in the wrong order, but that should get you there.

AllenG
flip the last two parameters
dotjoe
I figured I'd get part of it wrong. Edit forthcoming.
AllenG
A: 

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.

FrustratedWithFormsDesigner
A: 

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'
andreas
+3  A: 

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.

OMG Ponies
And SQLite uses `DATE('2010-07-29', '-1 day')`.
dan04
If t.date is date-only / set at midnight, these queries will return data for both 28 and 29 July 2010. Of course, if Skydoor told us a bit more about the problem...
Mark Bannister
@Mark Bannister: Only July 29th, 2010 entries at 00:00:00 would be included - nothing beyond that.
OMG Ponies
@OMG: in your Oracle query, replace `TABLE T` with `(select TO_DATE('2010-07-29', 'YYYY-MM-DD') - (ROWNUM-1) "date" from dual CONNECT BY LEVEL <= 2) T` (and double-quote t.date; date is a reserved word in Oracle.) You should see rows for both 28 and 29 July 2010.
Mark Bannister
@Mark Bannister: Thx, but as-is the query is consistent with the other examples.
OMG Ponies
@OMG: exactly - all of them will return data for both 28 and 29 July 2010.
Mark Bannister
@Mark Bannister: *If* the value is July 29 2010 00:00;00 - yes. If the value is July 29th 2010 00:00:01or higher - No, these records will *not* be returned. That's an extremely small window for July 29th... for requirements that aren't clear in the first place.
OMG Ponies
@OMG: agreed - which is why my original comment said "If t.date is date-only / set at midnight" (I've seen plenty of systems that store dates *without* times in datetime fields) and why I also said "Of course, if Skydoor told us a bit more about the problem..."
Mark Bannister