PeterJ
Posts: 90
|
| Posted: 01/30/2005, 7:03 AM |
|
Can anyone offer advice on searching dates. Having browsed the forum there are a multitude of variations.
I have a DB with Field1 set as Date
I want to be able to search the DB via a search form where the user selects the date via a datepicker which enters the date as dd/mm/yy.
How can I extract the corresponding date from the DB?
Is 'Date' the correct format to store the date in the DB?
I'm not sure how the search form is searching the DB and in which format i.e. yyyy-mm-dd or dd/mm/yyyy. The data is stored as yyyy-mm-dd of course.
Thanks
|
 |
 |
PeterJ
Posts: 90
|
| Posted: 01/30/2005, 7:21 AM |
|
Solved the problem:
Set the search box to text, format to yyyy-mm-dd
When the user selects the date it is coverted from dd/mm/yyyy to yyyy-mm-dd
In the SQL statement amend the WHERE to yourdatefield = DATE_FORMAT('%{s_yourdatefield}', '%Y-%m-%d')
Worked for me - hope it helps someone
|
|
|
|
Nicole
Posts: 586
|
| Posted: 01/31/2005, 5:54 AM |
|
PeterJ,
You’re correct, in order to search on dates you need to convert date to a date format specific for a database, before date is passed to a database.
But you can do it without custom coding. Below are parameter settings which should be used for WHERE parameter when you search on date value. Please re-check the settings of WHERE parameter in your project.
Field
Name: DateFieldName
Type: Date
Format: select/type in the date format in which date are actually stored in a table. E.g.: mm/dd/yyyy
Operation: =
Parameter Source
Name: s_DateFieldName
Type: URL
Format: select/type in the format in which date is passed via URL. E.g.: m/d/yy
_________________
Regards,
Nicole |
|
|
|
PeterJ
Posts: 90
|
| Posted: 01/31/2005, 11:37 AM |
|
Thanks Nicole
I had not thought of that. I'll give it a go next time around.
Regards
PeterJ
|
|
|
|
|
