fabiokbca
Posts: 18
|
| Posted: 01/30/2012, 8:25 AM |
|
Hello all.
Firstly sorry for my bad english.
I'm brazilian and work with CCS 2 months ago.
My question is : i have a "search page" and i need search in field datetime with 2 dates controls.
Only one think dont play very well
if i search dates between 20/01/2012 and 28/01/2012, the results of records with 28/01/2012 in field date are not shown. Records only minor than 28/01/2012.
How to solve this?
|
 |
 |
andrewi
Posts: 162
|
| Posted: 01/30/2012, 2:42 PM |
|
Hi,
Does your date field contain date AND time?
If you are searching for "dates between 20/01/2012 and 28/1/2012" you will get
(examples)
- 20/01/2012 00:00
- 20/01/2012 09:30
- 21/01/2012 20:34
...
- 28/01/2012 00:00
but not
- 28/01/2012 00:01
- 28/01/2012 09:30
and so on.
If this is the cause then you need to modify your query so it searches for dates between 20/01/2012 00:00 and 28/01/2012 23:59:59
or, which is probably easier to set up in Codecharge, "date >= 20/01/2012 AND date < 29/01/2012"
(if your field doesn't contain a time portion, then disregard this post!)
|
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 01/30/2012, 11:48 PM |
|
In your WHERE sql criteria make sure that you have specified the database format for the date field and on the right hand side specify the format that your date fields/pickers use.
_________________
Central Coast, NSW, Australia.
|
|
|
|
fabiokbca
Posts: 18
|
| Posted: 01/31/2012, 3:27 AM |
|
Hi guys.
Well...andrewi its is my problem..my datetime field contains date and time and i need alter the parameters fields.
Users chooses space between dates to search in my database.
He choose :
(example)
begin date : 25/01/2012
end date : 27/01/2012
I can't alter the value of end date to '27/01/2012 23:59:59' before execute select to fill the grid.
In my query builder the format of parameters is 'dd/mm/yyyy' but i try all other.
|
|
|
|
datadoit
|
| Posted: 01/31/2012, 6:35 AM |
|
The logic is correct if it is searching for a "to" date. So there are
two fixes.
1. Let the users know that for the "to" date, choose a day ahead of what
they anticipate.
2. Add a day to the chosen "to" date in the resulting grid's Before
Build Select event.
|
|
|
|
fabiokbca
Posts: 18
|
| Posted: 01/31/2012, 7:16 AM |
|
Hi datadoit.
So i tought it but i receive orders and i cant do like your suggestion.
How can i change value of one parameters fields(date_time <= dt_final to date_time <= dt_final . '23:59:59') in sql expression?
My sql is it:
SELECT name, status.status AS status_status, date_time, destination, message FROM (messages INNER JOIN users ON messages.user_id = users.id_user) INNER JOIN status ON messages.status = status.id_status WHERE messages.date_time >= '{dt_inicial}' AND messages.date_time <= '{dt_final}' AND messages.user_id = {id_usuario} AND messages.status = '{s_status_status}' ORDER BY date_time
|
|
|
|
datadoit
|
| Posted: 01/31/2012, 8:59 AM |
|
This is how you would do it using the Visual Query Builder in Table
mode, not in SQL mode:
For your message.date_time and dt_final WHERE parameter, change it to an
Expression, and use:
" . ((CCGetParam("dt_final", "")) ? "messages.date_time <= '" .
CCFormatDate(CCDateAdd(CCParseDate(CCGetParam("dt_final", ""),
array("dd", "/", "mm", "/", "yyyy")), "+1 day"), array("yyyy", "-",
"mm", "-", "dd")) . "'" : "messages.date_time <= '" .
CCFormatDate(CCDateAdd(CCParseDate(date("Y-m-d"), array("yyyy", "-",
"mm", "-", "dd")), "+1 day"), array("yyyy", "-", "mm", "-", "dd")) .
"'") . "
This is assuming that your database stores dates in the yyyy-mm-dd
HH:nn:ss format, and that your search form is using the format dd/mm/yyyy.
What we're doing above is programatically adding a day to the chosen
date. However, we also needed to check if a date was chosen as the
final date, and if not, then default to tomorrow. We're using PHP's
ternary operator to do the if/then condition check. If there's a
parameter for dt_final, then use that date plus one day. If there isn't
a parameter for dt_final, then use tomorrow's date.
You can see what the expression looks like in the PHP code of your page
(not the events code).
|
|
|
|
fabiokbca
Posts: 18
|
| Posted: 02/01/2012, 5:03 AM |
|
Thanks a lot datadoit.
Its work very well.
|
|
|
|
|
