gulam
Posts: 55
|
| Posted: 10/23/2008, 1:09 PM |
|
Hi,
I am having a very weird problem. I have a simple search and grid form where I am trying to search based on a date (s_date) where all records matching the imput date should display. This all sounds is quite straight forward but in my case this just doesnt work. IEverytime I iput a date for which records exists in the db, it comes back with NO RECORDS FOUND.
have developed several small apps in CCS 2 and 3 using dates without any issues but have seen this issue in my 1st app in CCS 4. I just want to know whether dates are treated any differently in CCS 4 than in earlier versions before I start tearing my hair in frustration.
In the database the type is set to DATE (i.e. yyyy-mm-dd), the display format is set to dd/mm/yyyy.
The SQL generated is as follows:
SELECT *
FROM (((noticesarea INNER JOIN notices ON
notices.notices_id = noticesarea.notices_id) INNER JOIN area ON
noticesarea.notices_area_id = area.notices_area_id) INNER JOIN region ON
notices.region_id = region.region_id) INNER JOIN noticestype ON
notices.notices_type_id = noticestype.notices_type_id
WHERE notices.notices_date = '{s_notices_date}'
AND area.region_id = {s_area_region_id}
AND area.notices_area_id = {s_area_notices_area_id}
The other 2 search fields are working perfectly.
I would appreciate any advice.
Gulam
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 10/23/2008, 6:48 PM |
|
gulam
Have you tried running the query in MySQL directly (with parameters)??
Have you also sure that the date field is formated as the database expects it to be (maybe you should echo the date field so that you can see what the value really is)??
|
|
|
|
melvyn
Posts: 333
|
| Posted: 10/24/2008, 7:44 AM |
|
There's some issue which I can't detail exactly now. It affected me 2 months ago and I discovered this:
When you're formatting a date you must set it type as text.
control source typ: database column
control source: your_date
data type: text <==== this is by default Date and obviously must be date. if you format it then change to text. I'll check the old project later to detail the workaround.
format: mm/dd/yyyy
....
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
gulam
Posts: 55
|
| Posted: 10/24/2008, 7:58 AM |
|
Thanks for your suggestion Melwyn. I tried it without success.
I put in code to display the SQL in Before Execute Select to see the final SQL that is executed and to my shock I see it like this:
SQL:SELECT * FROM notices {SQL_Where} {SQL_OrderBy}
ORDER BY:
WHERE:notices_date = '10/23/2008 00:00:00'
1. My display date format is dd/mm/yyyy in locales as well in the search textbox properties
2. MySQL's field is set to DATE
3. Why and how does the system change the date that iI nput into the search field to mm/dd/yyyy hh:ss:nn format?
4. How do I correct this ?
I am really losing it!!!
Gulam
|
|
|
|
gulam
Posts: 55
|
| Posted: 10/24/2008, 9:52 AM |
|
I have sorted out this problem, the date format in the connection was wrong.
Thanks all.
Gulam
|
|
|
|
