searching between dates - current date not displayed 1

[ttrinh]

Hi
I have 2 drop down boxes in which users can select dates between which they can search something. My problem is that the search does not return any rows which have been entered on the current day ie if user wants to see what new items have been entered today it always return 0 results even if there are new entries for the day.Entries all other days are returned correctly Can anyone help ? Is there something I am not understanding about the date functions in cold fusion?

 

[ttrinh]

Hi
Just want to add something I noticed also
When I query the database from coldfusion I am unable to get results for the current day in any report. This makes me think it may be something about the server not registering the current day. can anyone throw some light on this??

 

[erwino]

Do you search for the dates between yesterday and tomorrow to get today's entrees? When searching between you need to search to the date after the last date to include that date. Post the between sql to have a look at if this is not the case.

 

[ttrinh]

Hi
Sorry I haven't replied earlier , got caught up with something else.
I have 2 sets of drop down boxes. User can search for items created between an openedfrom and openedto or alternatively they can just choose a date openedfrom. the sql is as follows for searching between 2 dates.
SELECT bin.binid, bin.description, bin.datecreated, bin.dateclosed, status.status, bintypes.typename
FROM bin, bintypes, status, busUnit, department
WHERE (bintypes.typeid = bin.bintype AND
status.statusid = bin.status AND
bin.mngUnit = busUnit.bUnitID AND
busUnit.bUnitDept = Department.deptID) AND
(TO_DATE('#dateformat(attributes.openedfrom, &quot;dd-mmm-yyyy&quot#', 'DD-MM-YYYY') - bin.datecreated <= 0 AND TO_DATE('#dateformat(attributes.openedto, &quot;dd-mmm-yyyy&quot#', 'DD-MM-YYYY') - bin.datecreated >= 0)

the latter part of the where clause is altered for if user only fills in the openedfrom date
(TO_DATE('#dateformat(attributes.openedfrom, &quot;dd-mmm-yyyy&quot#', 'DD-MM-YYYY') - bin.datecreated <= 0

Thanks

 

[ttrinh]

So you are saying that if I want to view todays entries I have to set the openedfrom date to be today and the openedto date to be tommorrow?
When I set date openedfrom to yesterday and date openedto to todays date I just get all of yesterdays entries and nothing for today. Is that just how CF date functions work?

Many thanks

 

[erwino]

If I am not mistaken, it's not the cold fusion but the sql, the between date function works as follows (correct me if I am wrong)
Between 01-01-2002 AND 02-01-2002 is betweeen 01-01-2002 00:00hr and 02-01-2002 00:00 hr, so this does not include the second of Jan 2002. If you want to find anything from the first including the second of Jan you have to search between the 1st and the (start of) the 3th.
Hope this explains it,
Erwin Erwin Oosterhoorn
Analyst Programmer,
Roller hockey'er, biker, ice hockey fan.

 

[Guest_imported]

Hi ttrinh,

I believe if you reverse the < and > in the clause, it should work.

Good luck!
AP

 

[ttrinh]

Hi
Thanks : ) That helps heaps, at least now I won't be searching round trying to see why the code won't return the current days results