date formating problem

[ianwest]

Hi.
I have a database which has two fields called event_start_date and event_end_date (every record has to have a start date, but not every record has to have an end date).

The problem I've got is that I want to only display records from today onwards (old records stay in the database until they're deleted, but they just don't display), but if I'm ordering records by event_start_date they go away if their event_start_date is before today. What I need, in pseudo-code, is this:

<cfif event_end_date IS NOT &quot;none&quot; AND [today's date] IS BETWEEN event_start_date [and] event_end_date>

write record

<cfelseif event_end_date IS &quot;none&quot; [check if record's event is today or after, and if it is display it]>

write record

</cfif>


ie check whether the event has an end date. if it doesn't, and event_start_date is after today, display record. If the event _has_ an end date, check to see whether today's date is between event_start_date and _event_end_date and if it is, display the record.

anyone any ideas?

Ian
[sig][/sig]

 

[giahan]

Hi Ian,

What db u use?
- Change date format of event_start_date and event_end_date to text format. (my recommendation)

- It's complicated if u use cfif. Filter it thru the cfquery:
<cfquery name=&quot;getmystart_end&quot; datasource=&quot;mydb&quot; >
SELECT *
FROM mytable
WHERE event_end_date IS NOT '')
AND (event_start_date<='#today#'
and '#today#'<= event_end_date)
</cfquery>
The same thing for the other work
<cfquery name=&quot;getmyEnd&quot; datasource=&quot;mydb&quot; >
SELECT *
FROM mytable
WHERE event_end_date IS '')
AND (event_start_date<='#today#'
and '#today#'<= event_end_date)
</cfquery>


then u can use cfif now
<cfif getmyEnd.RecordCount GE &quot;1&quot;>
display ur info
<cfoutput query=&quot;getmyEnd&quot;>#field1# #field2# ....</cfoutput>
</cfif>

<cfif getmystart_end.RecordCount GE &quot;1&quot;>
display ur info
<cfoutput query=&quot;getmystart_end&quot;>#field1# #field2# ....</cfoutput>
</cfif>

I hope this help

[sig]<p>GH ((-:<br><a href=mailto:giahan@hotmail.com>giahan@hotmail.com</a><br>(-:[/sig]