SQLTeam.com | Weblogs | Forums

Search By Date Range not working. Any ideas?

Greetings again experts,

I have a request for a slight modification of this stored procedure.

As you can see from the code below, we are using dynamic WHERE clause.
This way, a user can search by one or more search parameters.

All search params produce results except the date searches(Start tme and end time).

Date format or starttime and endtime is in the following format:

year-month-day hour:minutes:seconds.

Example:
2015-01-01 14:37:34

Each time I perform a search, I get no records found.

Any ideas what I need to modify on the WHERE clause to get desired results when searching with date range of between startttime and endttime?

Many thanks in advance

ALTER PROCEDURE [dbo].[spGetCalls]
@uuid varchar(50),
@callerlist varchar(50),
@phone varchar(50),
@start varchar(150),
@Endd varchar(100),
@calltype varchar(100)

AS
BEGIN

SELECT 
   qp.phone_number, 
   callerid, 
   qp.call_list_id, 
   qp.lastbridged,
   qp.startttime,
   camapign_id,
   q.queueName, 
   c.name as call_type, 
   connecttime, 
   qp.endtime, 
   CONVERT(TIME(0), DATEADD(SS,qp.duration,0),108) as duration, 
   CONVERT(TIME(0), DATEADD(SS,qp.fullduration,0),108) as fullduration,
   ca.campName,
   ct.call_termination_type,
   qp.roll_id, 
   (e.first_name +' '+ e.last_name) employee, 
   i.name as cause_code,
   box_id, 
   trunk_name, 
   qp.uuid, 
   customer_id, 
   s.name as telephonyservers
FROM cti.qpcdr qp 
   inner join cti.call_types c on qp.call_type_id = c.id 
   inner join cti.queues q on qp.queue_id = q.queueId 
   inner join cti.campaign ca on qp.camapign_id = ca.campaign_id 
   inner join cti.isdn_cause_code i On qp.cause_code = i.cause_code 
   inner join cti.employee e on qp.employee_id = e.employee_id 
   inner join cti.call_history ch On qp.call_list_id = ch.call_list_id 
   inner join cti.call_list cl on qp.call_list_id = cl.call_list_id 
   inner join cti.call_history_qpcdr_link cq On cq.call_history_id = ch.call_history_id 
   inner join cti.call_terminations ct On ch.call_termination_id = ct.call_termination_id 
LEFT JOIN cti.server_settings ss ON ss.value = qp.box_id AND ss.attribute = 'asterisk_box_id'
LEFT JOIN cti.servers s ON s.id = ss.server_id
WHERE 1 = 1
 AND ((@uuid IS NULL OR qp.uuid Like '%' + @uuid + '%')
 AND (@callerlist IS NULL OR  qp.call_list_id Like '%' + @callerlist + '%')
 AND (@phone IS NULL OR cl.phone_number Like '%' + @phone + '%')
 AND (@start IS NULL OR qp.startttime Like '%' + @start + '%')
 AND (@Endd IS NULL OR qp.endtime Like '%' + @Endd + '%')
 AND (@calltype IS NULL OR c.id Like '%' + @calltype + '%')
 AND cl.phone_number IS NOT NULL)
 END

What data type is starttime and endtime? If they are datetime then LIKE is not going to work. You'll have to use = or between or some other datetime compatible comparison. Or you'll need to format your passed in date like this 'Jan 01 2015 2:37PM'

You might try something like this:
AND (@start IS NULL OR qp.startttime >= @start )
AND (@Endd IS NULL OR qp.endtime <= @Endd)

This type of dynamic search conditions can result in inefficient execution plans. You may want to search for "dynamic search conditions" and "parameter sniffing" There are some good articles on the topic written by Sommarskog, Gail Shaw etc.

Thank you for your prompt response.
data type for both startttime and datetime is datetime2(0)

I have attempted
(@start IS NULL OR qp.startttime = @start ) but no luck

James, I have also tried your solution but I actually tried it before your response.

Same issue = shows no data found.

Thank you so much for your response

You will need to post sample data then for the @start and @endd variables and the data in the table for starttime and endtime because = should work in that case

These are some actual results from startttime and endtime

startttime	                        endtime
2014-07-02 12:01:02	2014-07-02 12:01:44
2014-08-08 19:51:15	2014-08-08 19:53:10
2016-06-13 15:20:20	2016-06-13 15:34:40
2016-05-10 20:35:24	2016-05-10 20:37:23
2014-03-03 20:36:21	2014-03-03 20:38:49
2014-01-16 19:37:39	2014-01-16 19:39:30
2016-07-21 11:00:47	2016-07-21 11:01:17
2014-09-25 15:23:22	2014-09-25 15:26:37

Now, value added through my app is where I might be having issues.

We are using datepicker to select dates.

When displayed in a dropdown,

It displays values just as the values from the DB.

and this is what it looks like:

value="2015-12-31 16:54:47"

So, even when I copy the value from the DB and paste it on my app, it still does not pull any data.

It has to be the values you are passing in because this works just fine. Are you sure it's the datetime comparisons that are not being met or is it possible some other criteria in the where clause is causing the issue?

create table #temp (startttime datetime2(0), endtime datetime2(0))

Insert into #temp
values ('2014-07-02 12:01:02', '2014-07-02 12:01:44'),
('2014-08-08 19:51:15', '2014-08-08 19:53:10'),
('2016-06-13 15:20:20', '2016-06-13 15:34:40'),
('2016-05-10 20:35:24', '2016-05-10 20:37:23'),
('2014-03-03 20:36:21', '2014-03-03 20:38:49'),
('2014-01-16 19:37:39', '2014-01-16 19:39:30'),
('2016-07-21 11:00:47', '2016-07-21 11:01:17'),
('2014-09-25 15:23:22', '2014-09-25 15:26:37')

declare @start varchar(150) = '2015-12-31 16:54:47'
declare @end varchar(150) = '2019-06-25 16:54:47'

select * from #temp where startttime >= @start and endtime <= @end

Hi Bruce,

You are right that it is possible there is some issues with the datetime I am passing from the client side.

I neglected to mention that when I run the query in MSDS, I get correct results.

No, I am passing anything else.

If you look at that stored proc I posted, you can pass one or more search params and in this case, I am only passing date params.

I will look further to see if the JS has some hidden formats that I am not seeing.

I will report back.

Thanks for your continued assistance.

Just to test the values you're passing in save the @start and @endd off to a table and see what's actually reaching the stored proc.

Thanks again Bruce.

Here is a perfect example.

I queried the database and got this result: 2014-05-05 10:34:03

I went to the app on the browser, selected exact same value of 2014-05-05 10:34:03

However, when I run the app, it displays no data found

Not sure what is wrong

Are you still using like or did you change it to >= ? Did you save the @start and @endd variables in the stored procedure and see what the actual values are that have been passed into the stored proc?

Are you SURE this is a sql problem?

Run Profiler or an Extended Events session on the SQL box to capture what is being sent/returned to the sql server by the app when the query is run

Run the app in debug mode and step through the process...

You might be trying to fix a bug in the APP and if that is the case there is no amount of SQL Server changes that will help you