Otherwise it has to match to the exact second.
On Oracle:
WHERE TRUNC(event_date) = TO_DATE(<cfqueryparam
cfsqltype="CF_SQL_VARCHAR"
value="#Dateformat(show_event_date,'yyyy-mm-dd')#">,'YYYY-MM-DD')
On MSSQL you can do
WHERE DAY(event_date) = <cfqueryparam cfsqltype="CF_SQL_NUMERIC"
value="#Day(show_event_date)#">
AND MONTH(event_date) = <cfqueryparam cfsqltype="CF_SQL_NUMERIC"
value="#Month(show_event_date)#">
AND YEAR(event_date) = <cfqueryparam cfsqltype="CF_SQL_NUMERIC"
value="#Year(show_event_date)#">
OR (assuming that the time for show_event_date is 00:00:00)
WHERE event_date >= <cfqueryparam value="#show_event_date#"
cfsqltype="CF_SQL_TIMESTAMP">
AND event_date < <cfqueryparam value="#DateAdd('d',1,show_event_date)#"
cfsqltype="CF_SQL_TIMESTAMP">
If you don't need date and time combined, you could store them separatly
in the db. It takes up a little more space, but it would make your life
easier.
Pascal
PS It has been a very long time since I used dates on another db than
oracle, so you may want to check the syntax and the cfsqltype for MSSQL
PPS If you are asking questions like this, it is always a good idea to
mention the db you're using.
> -----Original Message-----
> From: Daniel Kessler [mailto:[EMAIL PROTECTED]
> Sent: dinsdag 25 mei 2004 21:24
> To: CF-Talk
> Subject: date search
>
> I am having a bit of trouble doing a date search on form entered data.
> Originally, I created a date with CreateDate and submitted
> it, then searched on it with a date created the same way
> (using WHERE event_date = #show_event_date#) and all was great.
>
> Then I was told to also collect the time. Who knew that
> events would need start times too? So, instead of
> createDate, I went to createDateTime. Since then, I've not
> been able to search on these items with my query that uses
> CreateDate. It doesn't seem right to have to include the
> time for searching, especially since I just want items on a
> particular date or series of date.
> I'm not sure how to go about forming a query on this, really.
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
