[thelist] SQL time problem

[Jeff Lucido]

I hope everyone had a nice holiday season and a smooth start to 2002.
However, I am having trouble trying to get a piece of SQL to work the
way  I want it to.  I am using SQL Server 2000 and I was hoping someone
may be able to shed some light on this problem.

The fileimage table contains the following relavent fields, fileimageid
(int), filedate (datetime), and filetime (datetime).  Filedate contains
the date the image, filetime contains the time of the image, but I need
to ignore the date.  In other word if I am searching by date and time, I
want to query the date from the filedate field and the time from the
filetime field.  Here is how I have it currently, it sort of works, but
has some glitches.  If anyone has any other suggestion please let me
know.

CODE! -- Watch out for the commented out lines.

<cfquery name="getInfo" datasource="#Request.appDS#">
	select a.imagefileid, a.imagefilename, b.abspath, c.relativepath
	from imagefile a, jobsite b, jobsiteCamera c
	where a.jobsiteid = b.jobsiteid
	and a.jobsitecameraid = c.jobsitecameraid
	and a.jobsiteid = c.jobsiteid
	<cfif form.jobsite neq "">
	 and a.jobsiteid = #form.jobsite#
	</cfif>
	<cfif form.camID neq "">
	 and a.jobsitecameraid = #form.camID#
	</cfif>
	<cfif form.from_date neq "">
	 and a.FileDate >= #CreateODBCDate(form.from_date)#
	</cfif>
	<cfif form.to_date neq "">
	 and a.FileDate <= #CreateODBCDate(form.to_date)#
	</cfif>
	<cfif form.from_time neq "">
	 <!---and FileTime >= #CreateODBCTime(form.from_time)#--->
	 and DatePart(hh,a.FileTime) >= #Hour(form.from_time)#
	 and DatePart(n,a.FileTime) >= #Minute(form.from_time)# 
	</cfif>
	<cfif form.to_time neq "">
	 <!---and FileTime <= #CreateODBCTime(form.to_time)# --->
	  and DatePart(hh,a.FileTime) <= #Hour(form.to_time)#
	 and DatePart(n,a.FileTime) <= #Minute(form.to_time)#
	</cfif> 
</cfquery>

Thanks for any help in advance!
-JSLucido