MySQL select by datetime?

[angst]

Hello,

Just moving my web site ( in ASP ) over to MySQL from Access,

and I'm having trouble with this query:

strsql ="select count(*) from transactions where transactions.BAdateStamper between #" & theMonth & "/" & i & "/" & theYear & " 00:00:01# and #" & theMonth & "/" & i & "/" & theYear & " 23:59:59# and active=1"


was hoping that someone might know what I'm going wrong here,

thanks in advance for your time!
-Ken

[angst]

ok,
well I tried this,

strsql ="select count(*) from transactions where BAdateStamper > #" & theMonth & "/" & i & "/" & theYear & " 00:00:01# and BAdateStamper < #" & theMonth & "/" & i & "/" & theYear & " 23:59:59# and active=1"


but still getting the same error:

select count(*) from transactions where BAdateStamper > #2/1/2006 00:00:01# and BAdateStamper < #2/1/2006 23:59:59# and active=1

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[TCX][MyODBC]You have an error in your SQL syntax near '' at line 1

/reports/transactionsByDay.asp, line 156

any ideas?
thanks again!
-Ken

[angst]

ok,
I've got that working,
just had to replace the ## with (''),

but this I'm having trouble with this query:

strsql ="select count(*), BACity, BAProvince from transactions where active=1 and CCcomplete=1 group by BACity, BAProvince order by BAProvince, count(*) desc;"

gives me this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[TCX][MyODBC]Invalid use of group function

reports/transactionsByDay.asp, line 227

not exactly sure what the problem is here,
any ideas?

thanks agian,
-Ken

[raf]

the only valid datetime format in MySQL must look like yyyy-mm-dd hh:mm:ss --> you can change the seperators and the number of digits for the year, but it should always be year-month-day

see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html
"Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98'). "
So you might get a 'correct'result now, it's not certain you'll always get a correct result.
For example; with your query, how should the rdbm know if that value is the first of february or the second of january?


for the group by, try:

strsql ="select count(*) as numrec, BACity, BAProvince from transactions where active=1 and CCcomplete=1 group by BACity, BAProvince order by BAProvince asc, numrec desc;"