MySQL RSS MySQL RSS

Some strange logical error in my code

Thread Solved

•
•
Join Date: Jan 2009
Posts: 3
Reputation: kjain is an unknown quantity at this point 
Solved Threads: 0
kjain kjain is offline Offline
Newbie Poster

Some strange logical error in my code

 
0
  #1
Jan 30th, 2009
Hi All,
I am mysql newbie.
Please have alook at this querry.

sql Syntax (Toggle Plain Text) 
  1. SELECT b.DATE as DATE, a.tirthankar_name as tirthankar_name, a.kalyanak as kalyanak
  2.        FROM table1 a, table2 b
  3.        WHERE a.mass = b.mass
  4.        AND a.paksh = b.paksh
  5.        AND a.tithi = b.tithi 
  6.         AND b.DATE  BETWEEN CURDATE( )
  7.       AND (CURDATE( ) +9)
  8.       ORDER BY b.DATE
  9.       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) 
  1. "AND b.date  BETWEEN CURdate( )
  2.       AND (CURdate( ) +9)"
to
sql Syntax (Toggle Plain Text) 
  1. "AND b.date  BETWEEN CURdate( )
  2.       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.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2008
Posts: 45
Reputation: DiGSGRL is an unknown quantity at this point 
Solved Threads: 4
DiGSGRL's Avatar
DiGSGRL DiGSGRL is offline Offline
Light Poster

Re: Some strange logical error in my code

 
0
  #2
Jan 30th, 2009
If you want the where to be contingent upon all the AND statements put them in parens.
eg:
sql Syntax (Toggle Plain Text) 
  1. WHERE (
  2. AND a.paksh = b.paksh
  3. AND a.tithi = b.tithi
  4. AND b.DATE BETWEEN CURDATE( )
  5. AND (CURDATE( ) +9)
  6. )
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.
A little clarification goes a long way.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 3
Reputation: kjain is an unknown quantity at this point 
Solved Threads: 0
kjain kjain is offline Offline
Newbie Poster

Re: Some strange logical error in my code

 
0
  #3
Jan 31st, 2009
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) 
  1. CURDATE()+70
gives entries corresponding to tomorrow.
MySQL Syntax (Toggle Plain Text) 
  1. CURDATE()+71
gives entries for day after tomorrow. similarly
MySQL Syntax (Toggle Plain Text) 
  1. 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.
A newbie Web Learner
Jains of World, Effective Resume
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 165
Reputation: Fest3er is an unknown quantity at this point 
Solved Threads: 18
Fest3er Fest3er is offline Offline
Junior Poster

Re: Some strange logical error in my code

 
0
  #4
Feb 9th, 2009
What does 'select curdate();' return?
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 1,341
Reputation: almostbob has a spectacular aura about almostbob has a spectacular aura about 
Solved Threads: 163
almostbob's Avatar
almostbob almostbob is offline Offline
Nearly a Posting Virtuoso

Re: Some strange logical error in my code

 
0
  #5
Feb 10th, 2009
sql Syntax (Toggle Plain Text) 
  1. 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
Failure is not an option It's included free
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it

Please mark solved problems, solved
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 165
Reputation: Fest3er is an unknown quantity at this point 
Solved Threads: 18
Fest3er Fest3er is offline Offline
Junior Poster

Re: Some strange logical error in my code

 
0
  #6
Feb 10th, 2009
No, not showing the definition. Rather, showing the current date. For example, on my desktop computer:
MySQL Syntax (Toggle Plain Text) 
  1. mysql> SELECT CURDATE();
  2. +------------+
  3. | CURDATE()  |
  4. +------------+
  5. | 2009-02-10 | 
  6. +------------+
  7. 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) 
  1. mysql> SELECT DATE_ADD(CURDATE(), INTERVAL -11 DAY);
  2. +---------------------------------------+
  3. | DATE_ADD(CURDATE(), INTERVAL -11 DAY) |
  4. +---------------------------------------+
  5. | 2009-01-30                            | 
  6. +---------------------------------------+
  7. 1 row IN SET (0.00 sec)

or

MySQL Syntax (Toggle Plain Text) 
  1. mysql> SELECT DATE_ADD(CURDATE(), INTERVAL +75 DAY);
  2. +---------------------------------------+
  3. | DATE_ADD(CURDATE(), INTERVAL +75 DAY) |
  4. +---------------------------------------+
  5. | 2009-04-26                            | 
  6. +---------------------------------------+
  7. 1 row IN SET (0.00 sec)

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

Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 3
Reputation: kjain is an unknown quantity at this point 
Solved Threads: 0
kjain kjain is offline Offline
Newbie Poster

Re: Some strange logical error in my code

 
0
  #7
Feb 11th, 2009
•
•
•
•
Originally Posted by almostbob View Post
sql Syntax (Toggle Plain Text) 
  1. AND b.DATE  BETWEEN CURDATE( )  AND  DATE_ADD(CURDATE(), INTERVAL +9 DAY)
This code worked.
Thanks Bob (Almostbob) and all other for your help.
A newbie Web Learner
Jains of World, Effective Resume
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the MySQL Forum
Thread Tools Search this Thread



agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm database design developer development distinct drupal dui ec2 email enterprise eudora facebook form foss gartner gnu government gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opensource oracle penelope php priceupdating query referencedesign reorderingcolumns resultset saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC