Query Date/Time Field 1

[boatguy]

Hi,

I need to query a date time field and pull all events happening during a particular day. These events get displayed to the user and I know I I have valid dates, but I don't get any results. I am thinking that my query is also looking at the time in the field, but I don't know how to query on the date only.

Here's my current query:

<CFQUERY name=&quot;today&quot; datasource=&quot;#dsn#&quot;>
Select *
From events
Where OrgID= '#ORGID#'
AND Eventdate='(NOW())'
</cfquery>

Any help would be appreciated.

 

[Sarky78]

you could change you query so that it looks like this:

<CFQUERY name=&quot;today&quot; datasource=&quot;#dsn#&quot;>
Select *
From events
Where OrgID= '#ORGID#'
AND Eventdate= #CreateODBCDateTime(Now())#
</cfquery>

this has working in the past for me

 

[CFDude]

What database are you using? If you are storing date and time, then doing it with = Now() will not work, since Now() returns a date and time.

Try

Where EventDate > #CreateODBCDate(Now()-1)# Will get everything for today

 

[boatguy]

Thanks to both of you, I am using a SQL 2000 database.

 

[boatguy]

Hi again,

I used the query structure recommended by CFDUDE and it pull all data past, present and future.

Here's what I am doing. I have an application written in MS Access in where a user makes entries and those entries are pumped up to the SQL database on my server.
The field in the SQL database eventdate is data type = varchar characters = 20 and the data supplied to that field looks like this 5/6/2002 0:00:00.

What gives?

Thanks

 

[CFDude]

Yuk.

The field type is not a date field, so you can do a couple of things:

Create a string &quot;date&quot; that equals the date you want to query on and then use that in the query.

e.g.

<cfset myDate = &quot;5/06/2002&quot;>
<cfset newDate = CreateDate(Year(myDate),Month(myDate),Day(myDate))>
<cfset qryDate = DateFormat(newDate,&quot;m/d/yyyy&quot & &quot; 0:00:00&quot;>

<CFQUERY name=&quot;today&quot; datasource=&quot;#dsn#&quot;>
Select *
From events
Where OrgID= '#ORGID#'
AND Eventdate='#qryDate#'
</cfquery>

Or, have SQL Server convert the datatype on the fly and then you could query against it:

<cfset myDate = &quot;5/06/2002&quot;>
<cfset qryDate = CreateODBCDateTime(myDate)>

<CFQUERY name=&quot;today&quot; datasource=&quot;#dsn#&quot;>
Select *
From events
Where OrgID= '#ORGID#'
AND cast(Eventdate as dateTime) = #qryDate#
</cfquery>

 

[boatguy]

Thanks to all, I have it working now.

Peace