DAMN! I wrote a LONG answer to this one and this idiotic VBulletin form swallowed it. AGAIN!
Again, what you are *COMPLETELY* missing is that you CAN NOT work on the cardeventdate and the cardeventtime as SEPARATE ENTITIES! You must construct a COMBINATION of the two.
*THINK* about your code!
There are only 4 seconds in two days that will meet the requirements of your query!
NO OTHER date or time will satisfy your UGLY condition:
sdate at 03:00:00
sdate at 03:00:01
edate at 03:00:00
edate at 03:00:01
Since you say your cardeventtime field is *ALWAYS* 6 characters long (even at 000003? 3 seconds after midnight??), there are two ways to approach this:
WHERE T_CARDEVENT.CARDEVENTDATE BETWEEN '6/4/2009' AND '6/5/2009'
AND T_CARDEVENT.CARDEVENTTIME BETWEEN '030001' AND '030000'
The preferred way, convering your ugly VARCHAR fields to DATETIME (and this is just the *BASIC* part of the query):
Or you can do it with string concatenation. Since you are using & to string together your strings, I assume you are using VBScript (ASP) or VB.NET (ASP.NET).
SELECT * FROM T_CARDEVENT
WHERE DATEADD( second,
+ CONVERT(INT,SUBSTRING(cardeventtime,5,2)) ),
CONVERT(DATETIME, cardeventdate, 112)
) BETWEEN '6/4/2009 03:00:01' AND '6/5/2009 03:00:00'
Hopefully, starting from that basis, you can see how to JOIN to your other table and get the right answer.
isosdate = Year(sdate) * 10000 + Month(sdate) * 100 + Day(sdate)
isoedate = Year(edate) * 10000 + Month(edate) * 100 + Day(edate)
SQL = "SELECT * FROM T_CARDEVENT " _
& " WHERE cardeventdate + cardeventtime " _
& " BETWEEN '" & isosdate & "030001' AND '" & isoedate & "030000' "
If not, ask again.