date range question

[jasn_78]

hey i have a query that depending in a radio box filters by two seperate date fields one is a short date (update date) and one is a long date time field (timestamp) when i choose to filter by (update) all is good but when i choose to filter by (timestamp) it excludes the 1st and last day of my date range.

Why does this happen with a <= and >= for the date time field and how do i fix it?

thanks everyone

 

[boblarson]

If you are using Time then you have to realize that, if you filter by date then you are going to be, in effect, using

for example:
02/25/2008 12:00:00 AM which is midnight of that day and so to use <= would be anything earlier than that so if you had 02/25/2008 09:23:31 then it would not fall into that category because it is after midnight on that day. So, to include all parts for 02/25/2008 you would need to test for either <= 02/25/2008 11:59:59 PM or < 02/26/2008 12:00:00 AM.

And that means for a between 02/24/2008 And 02/26/2008 if it is a date/time field that would actually pull any records between

02/24/2008 12:00:00 AM AND 02/26/2008 12:00:00 AM (or effectively 02/25/2008 11:59:59 PM).

Hope that helps.

 

[jasn_78]

bob thanks that does help just now need to figure out how when a user enters a date to return it as the date time field to the query as i dont wont user to have to worry about entering the time field in

thanks again

 

[jasn_78]

bob, so is there anyway of when a user chooses a date it defaults to a specific time i.e if they choose 31/07/2007 it is 31/07/2007 11:59:59pm rather than 31/07/2007 12:00:00am?

I have been looking for last couple days and cant seem to figure out how.

Thanks
Jason

 

[namliam]

If your users enter only dates (not timestamps) and you want to include that day you can go 2 or 3 ways

- Teach
Teach your users to select the date UP TO BUT NOT INCLUDING.
Meaning if you want 31-jul-2007 you want to select start: 31-jul-2007 and end: 01-aug-2007

- Hardcode 1
Simply add 1 to the date entered by the user. Now the end date of 31 Jul automagicaly changes to 1-Aug to include 31 Jul.

- Hardcode 23:59:59
Simply add timevalue("23:59:59") to the date entered by the user to force it to be that time you desire not the default 12:00:00 AM

Hope this helps