lchurch
Posts: 20
|
| Posted: 07/02/2010, 9:47 AM |
|
My CCS project works against a SQL Server database, and I need to search for records by date.
The DB column uses the DateTime, to store data as as "04/11/2010 11:17:22 AM". In the search form I set the Format to be mm/dd/yyyy and the DB format to be mm/dd/yyyy hh:mm:ss. But a search on a valid date returns no records. What do I need to change?
|
 |
 |
ckroon
Posts: 869
|
| Posted: 07/02/2010, 11:29 PM |
|
Use LIKE instead of = in your search query.. that might work.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
Waspman
Posts: 948
|
| Posted: 07/03/2010, 4:51 AM |
|
So the grid that runs the query gets the date in which format?
Did you tell the grid what to expect?
_________________
http://www.waspmedia.co.uk |
|
|
|
lchurch
Posts: 20
|
| Posted: 07/04/2010, 1:21 PM |
|
LIKE did not solve the search problem. The Grid shows data in the format mm/dd/yyyy.
|
|
|
|
Waspman
Posts: 948
|
| Posted: 07/08/2010, 6:26 AM |
|
So you did tell the grid what format the date would be in the URL?
_________________
http://www.waspmedia.co.uk |
|
|
|
songohan
Posts: 89
|
| Posted: 11/28/2011, 11:49 AM |
|
THANK YOU! :)
|
|
|
|
cvboucher
Posts: 191
|
| Posted: 11/30/2011, 3:15 PM |
|
In my CodeCharge apps when filtering on dates I use a couple of database functions
ALTER FUNCTION [dbo].[beginning_of_day] (@dtDateTime As datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(DAY, DATEDIFF(DAY, 0, @dtDateTime), 0)
END
and
ALTER FUNCTION [dbo].[end_of_day] (@dtDateTime As datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(ss, 86399, dbo.beginning_of_day(@dtDateTime))
END
Then, in CodeCharge I change the Datasource from Table to SQL and use the following SQL in the where clause.
WHERE DateCol BETWEEN dbo.beginning_of_day({s_StartDate}) AND dbo.end_of_day({s_EndDate})
If DateCol is indexed, the index will be used.
HTH,
Craig
|
|
|
|
|
