Tigra Calendar Product Family - Tigra Calendar Product - (1) thanks! (2) How I queried records within a date-range


	\| About Us \| Services \| Download \| Order \| Support \| \|

Not logged in [ Login | Register ]

SoftComplex Forums » Tigra Calendar Product Family » Tigra Calendar Product » (1) thanks! (2) How I queried records within a date-range

Last active: Never

Printable Version | Subscribe | Add to Favourites

bobdog

Newbie

Posts: 1
Registered: 2/20/2005
Member Is Offline

posted on 2/20/2005 at 08:38 PM

(1) thanks! (2) How I queried records within a date-range

Thanks folks for a really nice calendar. Good Job! I am using the free version on a community college website as a date-range picker to query an ODBC database for records >=#dat1# 00:00:00 and <=#dat2# 23:59:00, and it works beautifully.

For anyone having trouble making a date-range picker that queries the entire 24-hour period of the ending date, here is what is going on, and what I did to make it work for me (ColdFusion-specific and ODBC database):

First, you can't have the calendar put the time into the form field because that calendar function returns current system time--which won't give you the entire 24-hour period of your ending date. ie: if you pick your dates to populate a query at 10:00AM, you won't get any records from your end-date that were written after 10:00AM.

And, if you have the calendar give you dates only--no time, ie: 02/17/2005, the ODBC time defaults to 00:00:00, or 12:00AM. Thus, NO records from your chosen end-date will be returned from your query!

What to do?

Well, if you are using ColdFusion, the following code takes a date in the format 02/17/2005, and converts it to a format that the ODBC can use:

<cfset new_dat = #createODBCdatetime(02/17/2005)#>
--Gives the string output:
{ts '2005-02-17 00:00:00'}

Now, we're getting somewhere.

This is fine for the start date since a 00:00:00 start time will return all records for the entire day of your start-date.

But you need to go a step further to query your entire end-date. To do this, you have to change the 00:00:00 to 23:59:00 -- which changes ODBC time from midnight at the start of the day to a minute before midnight at the end of the day.

Here is what I came up with to do it:

Let's say that #dat2# IS 2/20/2005

<cfset endDate = '#dat2# 11:59 pm'>
<cfset timeEnd = #createODBCdatetime(#endDate#)#>

This gives: {ts '2005-02-20 23:59:00'}
or one-minute before midnight on the end date (you can go to further precision to minutes and milliseconds, if you need to).

You can now query for your entire date-range: from midnight on the starting date to a minute before midnight on the ending date.

Best Wishes.

NormanNewell

Junior Member

Posts: 3
Registered: 4/21/2005
Location: Ireland
Member Is Offline

posted on 4/22/2005 at 10:12 AM

How did you ahieve it bob can u give us an example

Hi Bob
I have sent you a U2U as well but i am trying to do the same thing as you and would appreciate it if you could either show or send some code
kinde regards
Norman