time range search

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>

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.