time range search

[kt03]

i need to create the ah hoc report that search for date range and time range but i don't know how to start with the time range since I nomrally work with the date range with pickcalendar for user to pick the from date and to date.

the values in the my table look like this

2002-09-13 16:26:00
2005-09-13 16:45:00
2006-09-13 17:54:00
2007-09-13 11:22:00
2009-09-12 16:05:00
2010-09-12 16:06:00
2011-09-12 16:06:00
if i create the free text box for them to enter time, then what would i need to validate to make sure they enter correct like 1 or 13 and PM. AM?. ect.

if i create the text box for them to select then what would i have in the text box and what's about PM or AM?

any sugesstion?

thanks

[ilssac]

kt03 wrote:

values in the my table look like this

2002-09-13 16:26:00

IF that is true.  I.E. the values are truly text strings such as your examples stored as some form of character data type, then you are going to have a tough time.  You will need to do some ugly string and|or cast processes to get where you want.

IF your data is properly some type date-time data type (and your examples are just the standard date-time output for your DBMS) then just a simple

date-time-field BETWEEN date-time-value AND date-time-value should get you what you want.

You may need to use some of ColdFusion's date-time functions to properly construct the desired date-time-values from whatever user input you are accepting.

[kt03]

the field is designed as smalldatetime format, so i can use date-time-field BETWEEN date-time-value AND date-time-value ?, not really familiar with this.  But on the form, i already have have two check boxes for date range search fo search from date and to date.  what i am thinking is add another 6 small drop boxes for time range:

from hh:  mm: AM/PM

to: hh: mm: AM/PM

how can i combine them into date-time-field BETWEEN search?

[Dan_Bracuk]

Forget what's on your form for now and focus on the type of search you are trying to create.  Is it something like, "tell me what happened between 11 AM and 2 PM during the 1st to the 5th of this month"?

If so, the answer lies in the date functions in your database software.  These vary of course but you can easily find them by googling "your db date functions"  You are looking for something that extracts the date and time portions of a datetime field.  Likely candidates are date(), time(), datename(), or datepart().

Once you figure out what function you need and how to use it, you can worry about the User Interface. 

[ilssac]

As Dan suggests, I usually start with a hard coded SQL statement the queries the database for the desired information.  Then it is usually pretty easy to look at the SQL code, see what parts need to by dynamic and plan how to create an user interface to collect the need information.

For Date-Time tasks, one way or another (and you will have many options) you will be creating a Date-Time string and then telling either ColdFusion or the database that this string represents a date-time, please make it a date-time value.

From ColdFusion's side you will have options such as the createdate(), createTime(), createDateTime(), parseDateTime and the somewhat anachronistic createODBCdate(), createODBCtime() and createODBCdateTime() functions.  Database management systems will have similar functions and others that may be more useful to your exact situation.

[BKBK]

kt03 wrote:

i need to create the ah hoc report that search for date range and time range but i don't know how to start with the time range since I nomrally work with the date range with pickcalendar for user to pick the from date and to date.

the values in the my table look like this

2002-09-13 16:26:00
2005-09-13 16:45:00
2006-09-13 17:54:00
2007-09-13 11:22:00
2009-09-12 16:05:00
2010-09-12 16:06:00
2011-09-12 16:06:00
if i create the free text box for them to enter time, then what would i need to validate to make sure they enter correct like 1 or 13 and PM. AM?. ect.

if i create the text box for them to select then what would i have in the text box and what's about PM or AM?

any sugesstion?

thanks

You can determine what the user does, in such a way as to avoid misunderstanding. There are 2 obvious and simple ways to do it. They're both by using select fields.

1) 24-hour time format: 00:00 to 23:59 (hours: 00, 01, ..., 23; minutes: 00, 01, ..., 59)

<select name="hr">

     <option value="00">00</option>

     <option value="01">01</option>

...

...

...

     <option value="23">23</option>

</select>

<select name="min">

     <option value="00">00</option>

     <option value="01">01</option>

...

...

...

     <option value="59">59</option>

</select>

2) 12-hour AM-PM time format (hour: 1, 2, ..., 12; minutes: 00, 01, ..., 59)

<select name="hr">

     <option value="1">1</option>

     <option value="2">2</option>

...

...

...

     <option value="12">12</option>

</select>

<select name="min">

     <option value="00">00</option>

     <option value="01">01</option>

...

...

...

     <option value="59">59</option>

</select>

<select name="meridiem">

     <option value="AM">AM</option>

     <option value="PM">PM</option>

</select>