TBMH
Posts: 40
|
| Posted: 09/17/2007, 10:39 AM |
|
I have a data grid and I'm using the Search Builder to allow my users to search data. One of the search criteria is date. This has a few problems - 1) the date that is selected appears in the URL and the "/" are replaced by "%2f". The second issue is that I'm using SQL Server, so all dates have a time stamp. Therefore, the user select a date (like 9/13/2007), but there is not a value for that in the database - the value is 9/13/2007 12:08
Any ideas on how to get around this and use a date as search criteria?
Thanks,
Scott
|
Edd
Posts: 547
|
| Posted: 09/19/2007, 12:46 AM |
|
Forget the "%2f" as that is standard encoding for querystrings, it is converted back to a "/" for CCS.
Re the dates, this is my preferred method:
1. In the grid, for each of your date parameters you will see the format to be passed to SQL (the one on the left hand side), change that to the SQL standard format YYYY-MM-DD.
2. Convert your grid from tables to SQL.
3. In the SQL change the SQL's where statement from something like is:
where trandate >= '{yourSearchStartDateParameter}' and trandate <= '{yourSearchEndDateParameter}'
to this
where Convert(Char(10),trandate,120) >= '{yourSearchStartDateParameter}' and Convert(Char(10),trandate,120) <= '{yourSearchEndDateParameter}'
The "Convert" verb changes the Date 9/7/2000 12:04PM to a 10 character string in the form of "YYY-MM-DD" so the date will be compared as 2007-09-07.
You could use the between operator if you wish - the above is for readability only.
Hope that helps.
Cheers - Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
