mySQL Selecting between two DATETIME

Question

I am using python and mysql connector and am trying to get all eventids from the mySQL DB between two different datetime stamps.

The DB is

 eventid | date | homeodds | drawodds | awayodds

This is the code:

def findarbs(startdate, enddate):
cnx = mysql.connector.connect(**dbconfig)
cur = cnx.cursor()
command = "SELECT DISTINCT eventid, date FROM moneyline 
               WHERE 'date' > ('%s') AND 'date' < ('%s') 
               ORDER BY date asc" % (startdate, enddate)
print command
cur.execute(command)
result = cur.fetchone()
print result
while result is not None:
    print result[1]
    result = cur.fetchone()

And the call to the method:

findarbs("2015-03-01 15:00:38", "2015-03-08 15:00:38")

Now if I change the command to:

command = "SELECT DISTINCT eventid, date FROM moneyline 
               WHERE 'date' > ('%s') 
               ORDER BY date asc" % (startdate)

it sucessfully selects the correct eventids and dates. If I change it to less than it also works. But when I try to combine them or use the BETWEEN command I get NONE back.

The code looks fine. Are you certain there are actual records in the DB that fall between that date range? — dursk
– dursk, Commented Mar 9, 2015 at 15:11
Yes I'm sure there are. That was what I was hoping, would be an easy fix. — user1235720
– user1235720, Commented Mar 9, 2015 at 15:15
The issue was having date in ' ' I removed the ' ' and it now works. I really should rename date column. — user1235720
– user1235720, Commented Mar 9, 2015 at 15:22
@Vuzuggu yes you should :) while you at it remember that it's all too easy to do the same in python - id, dir - some of the big offenders... — user3012759
– user3012759, Commented Mar 9, 2015 at 15:48

Rick James · Accepted Answer · 2015-03-10 05:41:31Z

2

Wrong type of quotes:

'date'

-->

`date`

'date' is a string that evaluates as 0.

Also, not that neither :38 is captured by > and <.

answered Mar 10, 2015 at 5:41

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mySQL Selecting between two DATETIME

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related