Some strange logical error in my code

[kjain]

Hi All,
I am mysql newbie.
Please have alook at this querry.

SELECT b.date as date, a.tirthankar_name as tirthankar_name, a.kalyanak as kalyanak
       FROM table1 a, table2 b
       WHERE a.mass = b.mass
       AND a.paksh = b.paksh
       AND a.tithi = b.tithi 
        AND b.date  BETWEEN CURdate( )
      AND (CURdate( ) +9)
      ORDER BY b.date
      LIMIT 0 , 1000;

I am trying to run this querry but it is giving zero rows as output while I am running it on my table.
It was running fine a few days ago but started bugging around 7-8 days back when curdate and curdate+9 range was in January month it self.

I assume the problem is mainly due to change in month from January to February which is causing this trouble.
Is there any better way to code this.
I want to put a few variables where date is today or 9 days from today (that is for next ten days including today).
The date field has values 2009-01-30 for today in both the tables.

Also it is runs and gives output if I change

"AND b.date  BETWEEN CURdate( )
      AND (CURdate( ) +9)"

to

"AND b.date  BETWEEN CURdate( )
      AND (CURdate( ) +75)"

Although does not gives output for next 75 days but it does output for next 5 days.

Its a bit urgent

[DiGSGRL]

If you want the where to be contingent upon all the AND statements put them in parens.
eg:

WHERE (
AND a.paksh = b.paksh
AND a.tithi = b.tithi
AND b.date BETWEEN CURdate( )
AND (CURdate( ) +9)
)

[kjain]

Thanks for suggestion. But it did not work as well.
I figured out while working that it is working like

curdate()+70

gives entries corresponding to tomorrow.

curdate()+71

gives entries for day after tomorrow. similarly

curdate()+90

gives entries for twenty next days.
It suggest that the code is working with a shift of 69 in addition of Curdate().
Any clues for why this is happening.

[Fest3er]

What does 'select curdate();' return?

[almostbob]

AND b.date  BETWEEN CURdate( )  AND  DATE_ADD(CURDATE(), INTERVAL +9 DAY)

[Fest3er]

No, not showing the definition. Rather, showing the current date. For example, on my desktop computer:

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2009-02-10 | 
+------------+
1 row in set (0.00 sec)

If there's an inexplicable discrepancy in the date, I would first check to be sure the SQL server's date is correct.

Upon finding the server's date is correct, I would RTFM and google until I found references to adding and offsetting dates. I would find that one can't simply add to curdate(). One needs to use, yes, another function:

mysql> select DATE_ADD(CURDATE(), INTERVAL -11 DAY);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL -11 DAY) |
+---------------------------------------+
| 2009-01-30                            | 
+---------------------------------------+
1 row in set (0.00 sec)

or

mysql> select DATE_ADD(CURDATE(), INTERVAL +75 DAY);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL +75 DAY) |
+---------------------------------------+
| 2009-04-26                            | 
+---------------------------------------+
1 row in set (0.00 sec)

Once the OP uses the correct functions, the code should begin to work.

:)

[kjain]

AND b.date  BETWEEN CURdate( )  AND  DATE_ADD(CURDATE(), INTERVAL +9 DAY)

This code worked.
Thanks Bob (Almostbob) and all other for your help.