Querying a timestamp

[trendline]

Hello all, Having a problem querying a timestamp for a date. This is my
query: SELECT * FROM accessLog WHERE td = '03/05/05' In the database, td is a
timestamp. The query is not returning any results, although I know there are
several hundred entries for that date. Could someone please tell me what I am
doing wrong? Thanks.

[philh]

Well, the only record you should return with that query is for March 5th, 2005
exactly at midnight. A datetime or timestamp field contains the date AND time,
in some cases down to the millisecond ('ts 03/05/05 00:00:00.000'). If you
just want records by date, you'll have to use the BETWEEN operator. So, for
March 5th, 2005, the query should read SELECT * FROM AccessLog WHERE td
BETWEEN '03/05/2005' AND '03/06/2005' The reason you do this is because the
database engine assumes midnight if you don't supply the time. So, to
parameterize for what I assume is a passed date, SELECT * FROM AccessLog
WHERE td BETWEEN '#form.querydate#' AND dateadd(d,1,'#form.querydate#') Check
your flavor of DB for the existence of and syntax regarding DATEADD(). You can
use CF date manipulation functions if necessary. HTH,

[trendline]

Thanks for the answer. I just realized myself that in using the
CreateODBCDateTime function, I got 00:00:00 for the time, hence there would be
no match. You technique will work... I'm wondering instead of using the
between function as you put it, I thought I would a less than greater than
function to isolate the day. td < dateAdd(d,1,#form.queryDate#) td >
dateAdd(d,-1,#form.queryDate#) Is there some sort of DatePart function I can
use on td to pull out it's date? Something like DatePart(date, td) = '03/05/05'
?

[philh]

BETWEEN is perfect for this sort of evaluation; it's the equivalent of >= |
<=. There is no datepart for the calendar date. You'd have to construct it
out of the various date parts (mm, dd,yyyy). I don't see why you need to do
this. When you display it, use DateFormat(). When you query, don't worry
about it because BETWEEN takes care of it.

[trendline]

I have already implemented your suggestion and it works fine, I just wanted to
educate myself by trying to see if there was some type of SQL or Coldfusion
function to extract the date part from the timestamp. Something like the way
the DatePart function pulls out a specific part of a date... didn't know if
there was a function that would pull the date out of a timestamp. Thanks for
the help.