[Tutor] Dates

[Bob Gailer]

John CORRY wrote:
> Hi All,
>
> I am using the code below to select items from a visual foxpro database
> where the dates are between the 31/01/2006 and 31/12/2006.  The good
> news is that the code below works.
>
> However, I want to make the from and to dates variable.  I want to
> change the range depending on user input.  I can't get this to work. I
> have tried the code below marked "Tried" but I get the error:
>
> Traceback (most recent call last):
>   File "C:\test\timemanager.py", line 16, in ?
>     c.execute('SELECT * FROM times where rt_weekst >= ? and rt_weekst <=
> ?  and rt_type == ?', (a,b,"R",))
> DataError: ('22005', 301, '[Microsoft][ODBC Visual FoxPro
> Driver]Operator/operand type mismatch.', 4579)
>
> Code that works is below:
>
>
> import mx.ODBC
> import mx.ODBC.Windows
> import mx.DateTime
>
>
>
> db = mx.ODBC.Windows.DriverConnect('DSN=tnt')
> c = db.cursor()
> c.execute('SELECT * FROM times where rt_weekst >= date(2006,01,31) and
> rt_weekst <= date(2006,12,31)  and rt_type == ?', ("R",))    
> for row in c.fetchall():
>     print row
>     row = str(row)
>    
> c.close()    
>
>
> Tried but get errors:
>
> import mx.ODBC
> import mx.ODBC.Windows
> import mx.DateTime
>
> import datetime
> a = datetime.date(2006,01,31)
> b = datetime.date(2006,12,31)
> db = mx.ODBC.Windows.DriverConnect('DSN=tnt')
> c = db.cursor()
> c.execute('SELECT * FROM times where rt_weekst >= ? and rt_weekst <= ?
> and rt_type == ?', (a,b,"R",))    
> for row in c.fetchall():
>     print row
>     row = str(row)
>     
> c.close()   
>
> Is there a way to format the date so that the Select statement works?
>   
a = "date(%i,%i,%i)" % (2006,01,31)
b = "date(%i,%i,%i)" % (2006,12,31)

sql = 'SELECT * FROM times where rt_weekst >= %s and rt_weekst <= %s and rt_type = ' % (a,b,"R")
c.execute(sql)

OR you could shorten it a bit:
sql = 'SELECT * FROM times where rt_weekst between %s and %s and rt_type = "%s"' % (a,b,"R")

I like to assign sql first, then execute it, as I can inspect sql if there is a problem. 

FoxPro also recognizes date constants like {12/31/2006} (note braces not 
parentheses).

-- 
Bob Gailer
510-978-4454