Ad:

MySQL Discussion Thread
Marked Solved
Views: 1011

Jan 30th, 2009

Some strange logical error in my code

Expand Post »

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

sql Syntax (Toggle Plain Text)
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;
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

sql Syntax (Toggle Plain Text)

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

sql Syntax (Toggle Plain Text)

"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

Last edited by peter_budo; Jan 30th, 2009 at 2:39 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

kjain

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jan 2009

Permalink

Jan 30th, 2009

Re: Some strange logical error in my code

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

sql Syntax (Toggle Plain Text)
WHERE (
AND a.paksh = b.paksh
AND a.tithi = b.tithi
AND b.DATE BETWEEN CURDATE( )
AND (CURDATE( ) +9)
)
WHERE (
AND a.paksh = b.paksh
AND a.tithi = b.tithi
AND b.date BETWEEN CURdate( )
AND (CURdate( ) +9)
)

Last edited by peter_budo; Feb 2nd, 2009 at 7:09 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

DiGSGRL

Reputation Points: 10

Solved Threads: 4

Light Poster

Offline

45 posts
since May 2008

Permalink

Jan 31st, 2009

Re: Some strange logical error in my code

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

MySQL Syntax (Toggle Plain Text)
CURDATE()+70
curdate()+70

gives entries corresponding to tomorrow.

MySQL Syntax (Toggle Plain Text)
CURDATE()+71
curdate()+71

gives entries for day after tomorrow. similarly

MySQL Syntax (Toggle Plain Text)
CURDATE()+90
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.

kjain

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jan 2009

Permalink

Feb 9th, 2009

Re: Some strange logical error in my code

What does 'select curdate();' return?

Fest3er

Reputation Points: 51

Solved Threads: 35

Posting Whiz in Training

Offline

238 posts
since Aug 2007

Permalink

Feb 10th, 2009

Re: Some strange logical error in my code

sql Syntax (Toggle Plain Text)
AND b.DATE  BETWEEN CURDATE( )  AND  DATE_ADD(CURDATE(), INTERVAL +9 DAY)
AND b.date  BETWEEN CURdate( )  AND  DATE_ADD(CURDATE(), INTERVAL +9 DAY)

Last edited by almostbob; Feb 10th, 2009 at 7:13 pm. Reason: dammit I cant spell

almostbob

Reputation Points: 562

Solved Threads: 367

Posting Maven

Online

2,970 posts
since Jan 2009

Permalink

Feb 10th, 2009

Re: Some strange logical error in my code

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

MySQL Syntax (Toggle Plain Text)
mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2009-02-10 | 
+------------+
1 row IN SET (0.00 sec)
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 Syntax (Toggle Plain Text)
mysql> SELECT DATE_ADD(CURDATE(), INTERVAL -11 DAY);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL -11 DAY) |
+---------------------------------------+
| 2009-01-30                            | 
+---------------------------------------+
1 row IN SET (0.00 sec)
mysql> select DATE_ADD(CURDATE(), INTERVAL -11 DAY);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL -11 DAY) |
+---------------------------------------+
| 2009-01-30                            | 
+---------------------------------------+
1 row in set (0.00 sec)

MySQL Syntax (Toggle Plain Text)
mysql> SELECT DATE_ADD(CURDATE(), INTERVAL +75 DAY);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL +75 DAY) |
+---------------------------------------+
| 2009-04-26                            | 
+---------------------------------------+
1 row IN SET (0.00 sec)
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.

Fest3er

Reputation Points: 51

Solved Threads: 35

Posting Whiz in Training

Offline

238 posts
since Aug 2007

Permalink

Feb 11th, 2009

Re: Some strange logical error in my code

Click to Expand / Collapse Quote originally posted by almostbob ...

sql Syntax (Toggle Plain Text)
AND b.DATE  BETWEEN CURDATE( )  AND  DATE_ADD(CURDATE(), INTERVAL +9 DAY)
AND b.date BETWEEN CURdate( ) AND DATE_ADD(CURDATE(), INTERVAL +9 DAY)

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

kjain

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jan 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Query for 3 tables

Next Thread in MySQL Forum Timeline: Count from multiple tables and group output by date

DaniWeb Message

Cancel Changes

User Name
Password