Im trying to do a query that recieves visitors from a table between 2 dates. However, Im getting an error saying:


Caused by: java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

I understand the fact that it says its caused by java.sql.SQLException, but here are the strange parts. This is the day format that im trying to get working.

sch_in_time >= '2006-07-07 00:00:00.000' and sch_out_time <='2006-07-07 23:59:59.000'

This throws that error. But if i cut/paste the query into query analyzer, everything works just fine. Also, if i have a date in the format of '2006-07-07 23 PM', it works just fine. This would almost be useable except it would leave people that were there between 11pm and 11:59pm.

Another tidbit of data is that im using the createSQL method in order to call this.

If anyone could give me some pointers, I would be extremely grateful. Thank you for your time.

Why don't you try this:
sch_in_time BETWEEN '2006-07-07' and '2006-07-08' which is exactly the same?
'2006-07-08' normally mean '2006-07-08 00:00:00.000' and in fact
( sch_in_time < '2006-07-08' ) means same as '2006-07-07 23:59:59.999'

unfortunately id have to check against sch_in_time and sch_out_time :(

the between idea would almost work but since people can be scheduled across multiple days (monday through friday), i dont want it crossing over with other people so i couldnt really do a:

(sch_in_time between 7/7/06 and 7/15/06 ) and (sch_out_time between 7/7/06 and 7/15/06)

Hi tont,
May i know whether u had find a solution for the problem u faced? I face the same the problem now and hope u can provide the solution for me. Thanks a lot....:)

Best regrads,
Suanne