Retrieving data via date 2

[peter11]

I want to pull data from a data base according to date but my date field is a date/time field.

I tried the following but you cannot use date format in the query. There must be a simple way to do this but I can't see it.

<cfset todaydate = #CreateODBCDate(Now())#>
<cfset currentdate = #DateFormat(todaydate, &quot;mm/dd/yyyy&quot#>

<cfquery name=&quot;today&quot; datasource=&quot;mycalendar&quot;>
select *
from EventsOneTime
where EventDate = #currentdate#
</cfquery>

 

[ecobb]

What database? Try putting single quotes around you date variable, like this: '#currentdate#'



Hope This Helps!

Ecobb
- I hate computers!

 

[krisbrixon]

I only do one step less, but I don't know if it is better or worse.

<cfset currentdate = #DateFormat(Now(), &quot;mm/dd/yyyy&quot#>
<cfquery name=&quot;today&quot; datasource=&quot;mycalendar&quot;>
select *
from EventsOneTime
where EventDate = #currentdate#
</cfquery>

You could write a UDF,custom tag,... to format the date and call it in the query.

Kris

 

[peter11]

The problem is that the data is an access database, and the field is date/time.
Below is an example of an entry:
9/10/2003 8:00:00 PM

The above entry is compared with:
10/14/03

I need to eliminate the time aspect of the entry (via code because the database is already populated).

Peter

 

[krisbrixon]

oh, then you need to search for a range.

from EventsOneTime
where EventDate >= '#currentdate# 12:00:00 AM'
and EventDate <= '#currentdate# 11:59:59 PM'

I think something like that would work, I would check the times to make sure I was looking at my clock correctly.

Kris

 

[peter11]

That makes total sense but the single quotes creates the problem to type mismatch.

 

[ecobb]

I haven't dealt with Access in so long, I'm not even sure if this would work:

<cfquery name=&quot;today&quot; datasource=&quot;mycalendar&quot;>
select *
from EventsOneTime
where Month(EventDate) = Month(#currentdate#)
AND Day(EventDate) = Day(#currentdate#)
AND Year(EventDate) = Year(#currentdate#)
</cfquery>

Yeah, I know it's nasty, but try it and see if it works.

Hope This Helps!

Ecobb
- I hate computers!

 

[peter11]

no luck

 

[ecobb]

Are you getting any errors?

Hope This Helps!

Ecobb
- I hate computers!

 

[RayGibbins]

AS krisbrixon's reply, but replace the single quotes as follows

where EventDate >= ###currentdate## 12:00:00 AM#
and EventDate <= ###currentdate## 11:59:59 PM#

 

[peter11]

Ray,
This gives me the following error:

Invalid parser construct found on line 9 at position 48. ColdFusion was looking at the following text:

12

 

[peter11]

Here is my most recent attempt and the error generated:

===========Code=========
<cfquery name=&quot;today&quot; datasource=&quot;greatwebcalendar&quot;>
select *
from EventsOneTime
where EventOneDateTimeStart >= (#currentdate# 12:00:00 AM)
and EventOneDateTimeStart <= (#currentdate# 11:59:59 PM)
</cfquery>

============Error=========
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'EventOneDateTimeStart >= (10/14/2003 12:00:00 AM) and EventOneDateTimeStart <= (10/14/2003 11:59:59 PM)'.

 

[ecobb]

Try this:

<cfquery name=&quot;today&quot; datasource=&quot;greatwebcalendar&quot;>
select *
from EventsOneTime
where EventOneDateTimeStart >= '#currentdate# 12:00:00 AM'
and EventOneDateTimeStart <= '#currentdate# 11:59:59 PM'
</cfquery>



Hope This Helps!

Ecobb
- I hate computers!

 

[peter11]

No, when I use single quotes I get the following error:
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

 

[peter11]

Here is a new attempt. The problem here is &quot;this date&quot; is set to the last value in the eventdatetimestart field. I want it to represent every entry in that field as it loops.



<cfset todaydate = #CreateODBCDate(Now())#>
<cfset currentdate = #DateFormat(todaydate, &quot;mm/dd/yyyy&quot#>


<cfquery name=&quot;list&quot; datasource=&quot;greatwebcalendar&quot;>
select eventonedatetimestart
from EventsOneTime
</cfquery>
<cfloop query=&quot;list&quot;>
<cfoutput>
<cfset thisdate = #DateFormat(eventonedatetimestart, &quot;mm/dd/yyyy&quot#>
<cfquery name=&quot;today&quot; datasource=&quot;greatwebcalendar&quot;>
select eventonedatetimestart
from EventsOneTime
where #currentdate# = #thisdate#
</cfquery>
</cfoutput>
</cfloop>

 

[csteinhilber]

I guess I'm confused why you're using DateFormat after your CreateODBCDate. If you're comparing against a Date/Time field, you actually want the value in ODBC format.

By calling DateFormat(), you're converting your date into a string... which ain't what you want.

Have you tried it without the DateFormat?

<cfset todaydate = #CreateODBCDate(Now())#>
 
<cfquery name=&quot;today&quot; datasource=&quot;mycalendar&quot;>
select *
from EventsOneTime
where EventDate = #todaydate#
</cfquery>

It's supposed to work ;-)

The nice thing about Date/Time data is the time portion is assumed. If it's absent in the SQL, the driver should automatically assume that you're talking about midnight. So you don't need to do any further formatting.

If you need greater control over the time portion, MX has CreateODBCDateTime()... but I don't think that's available in 5.0 or below.



-Carl

 

[parrotgurl]

&quot;The nice thing about Date/Time data is the time portion is assumed. If it's absent in the SQL, the driver should automatically assume that you're talking about midnight. So you don't need to do any further formatting.&quot;

But we don't know about the values in the database. They could include times. I'm assuming they in fact do include times. You query will only return records where the time is midnight.

 

[parrotgurl]

If all the times in the db are set to midnight, then Carl's version works.

If not, I believe this will do it:
<cfset begindate = #CreateDateTime(DatePart('yyyy', Now()), DatePart('m', Now()), DatePart('d', Now()), 0, 0, 0)#>
<cfset enddate = #CreateDateTime(DatePart('yyyy', Now()), DatePart('m', Now()), DatePart('d', Now()), 23, 59, 59)#>

<cfquery name=&quot;today&quot; datasource=&quot;mycalendar&quot;>
select *
from EventsOneTime
where EventDate >= #begindate# and EventDate <= #enddate#
</cfquery>

 

[csteinhilber]

But now your back to a ColdFusion date... which doesn't always work with ODBC drivers... which is why CreateODBCDate() was, well, created.

Better to use CreateODBCDate and let it do it's time assumption thing:

<CFSET dtCFTodaysDate = now()>
<CFSET dtTodayAtMidnight = CreateODBCDate(dtCFTodaysDate)>
<CFSET dtTomorrowAtMidnight = CreateODBCDate(DateAdd('d',1,dtCFTodaysDate))>

<CFQUERY name=&quot;today&quot; datasource=&quot;mycalendar&quot;>
   SELECT *
   FROM EventsOneTime
   WHERE EventDate >= #dtTodayAtMidnight# 
   AND EventDate < #dtTomorrowAtMidnight#
</CFQUERY>
Will return all events that have a date between today @ 00:00 and tonight at 23:59.

-Carl