Query not returning record when searching range of dates

[tasawer]

Hi,
in my SQl database, I have a WHERE statement to search a record based on two dates.
using the code below, Query will not return a record if SearchDate=FromDate.

Amongst my records, an example date range in database is:

FromDate = 2014-04-10 17:32:55.000
ToDate=    2014-04-16 17:32:55.000

Search fails for 10/04/14.
Other dates (11/04/14, 12/04/14 etc) will be returned.

SQL Code

WHERE PHD.FromDate <= Convert(DateTime, '10/04/2014' ,103)      
AND PHD.ToDate >= Convert(DateTime, '11/04/2014' ,103)
 

VB2010 Code

" WHERE PHD.FromDate <= convert(DateTime, '" & txtPCNDate.Value.ToString("dd/MM/yyyy") & "' ,103)   " & _
"   AND PHD.ToDate >= convert(DateTime, '" & txtPCNDate.Value.AddDays(1).ToString("dd/MM/yyyy") & "' ,103)      " & _

What am I missing from the query?

[vmm1234]

From the code you are comparing the dates in Datetime format.

So

PHD.FromDate <= Convert(DateTime, '10/04/2014' ,103)

becomes

'2014-04-10 17:32:55.000' <= '2014-04-10 00:00:00.000'

You may want to change your code to

WHERE DATE(PHD.FromDate) <= Convert(DateTime, '10/04/2014' ,103)       
AND DATE(PHD.ToDate) >= Convert(DateTime, '11/04/2014' ,103) 
 

[tasawer]

Thanks for the reply.
Your code not work for me. However, I changed it to

Convert(DATE,PHD.FromDate) >= Convert(DateTime, '09/04/2014' ,103)       
AND Convert(DATE,PHD.ToDate) <= Convert(DateTime, '10/04/2014' ,103)
 

Where it found the example date, but other dates went missing.

Below is my full range of dates for one record. I need to be able to search for any date.
Please advise on the how the code can be changed and the logic.

 ID    FromDate            ToDate
9132    2014-02-13 17:32:55.000    2014-02-19 17:32:55.000
9133    2014-02-20 17:32:55.000    2014-02-26 17:32:55.000
9134    2014-02-27 17:32:55.000    2014-03-05 17:32:55.000
9135    2014-03-06 17:32:55.000    2014-03-12 17:32:55.000
9136    2014-03-13 17:32:55.000    2014-03-19 17:32:55.000
9137    2014-03-20 17:32:55.000    2014-03-26 17:32:55.000
9138    2014-03-27 17:32:55.000    2014-04-02 17:32:55.000
9139    2014-04-03 17:32:55.000    2014-04-09 17:32:55.000
9140    2014-04-10 17:32:55.000    2014-04-16 17:32:55.000
9141    2014-04-17 17:32:55.000    2014-04-23 17:32:55.000
9142    2014-04-24 17:32:55.000    2014-04-30 17:32:55.000
 

[Luuk]

you have the '<=' and '>=' wrong...

select current_timestamp 
where 
  current_timestamp between convert(DateTime, current_timestamp,103) and convert(DateTime,current_timestamp+1,103);
 

or

select current_timestamp 
where current_timestamp >= convert(DateTime, current_timestamp,103) 
  and current_timestamp <= convert(DateTime,current_timestamp+1,103);
 

[tasawer]

These suggestions also fail.
currently we are checking for both dates in one query.
My instincts tell me that I need to run a query on FROMDATE first and then Requery the TODATE
So I would need a Select statement within the where. but not sure how.

[Rabbit]

If you are looking for whether or not the time periods overlap at all, then you need to look for where the start of period A is before the end of period B and the end of period A is after the start of period B.

However, if you are looking for if one time period is fully encompassed by another time period, then you need to check that both the start and end of period A is between the start and end of period B.

And also, if you are looking at just dates, then you need to convert everything to dates and not datetime.

[Luuk]

I'm sorry, but i dont understand your question,
or do you mean to return all the info except for the records between two given dates?

Then you need:

WHERE PHD.FromDate <= Convert(DateTime, '10/04/2014' ,103)      
    OR PHD.ToDate >= Convert(DateTime, '11/04/2014' ,103)
 

or

WHERE NOT (PHD.FromDate BETWEEN Convert(DateTime, '10/04/2014' ,103)      
    AND PHD.ToDate >= Convert(DateTime, '11/04/2014' ,103))
 

[tasawer]

Thanks for your suggestions.
I finally resolved it with the code below.
It works a treat.

WHERE (PHD.FromDate <= CONVERT(Date, '17/04/2014' , 103) 
AND PHD.ToDate>=CONVERT(Date, '17/04/2014' , 103))
OR 
(Convert(Date, PHD.FromDate) = CONVERT(Date, '17/04/2014' , 103))
 

[Luuk]

Somehow i'm under the impression that you still do not know WHY/HOW you solved this.....

Am i correct?

[tasawer]

I am absolutely certain on how I resolved it.
it was tough though(for me).

I went through a range of eight sequential dates and monitored the output and changed the code accordingly.

As time is also stored with the date, it was causing some of the dates not to be returned.
I resolved that by converting datetime to date only and then comparing.

OR 
(Convert(Date, PHD.FromDate) = CONVERT(Date, '17/04/2014' , 103))
 

of course, if there is an improved solution, I'm all ears to it.

[Luuk]

Your last step is wrong.
Yes, it does produce the desired output, true!

Because you are using a function on a databasecolumn, the database will no longer use the index that you probable have defined on that field.

So i suggest rewrinting this:

 
    WHERE (PHD.FromDate <= CONVERT(Date, '17/04/2014' , 103) 
    AND PHD.ToDate>=CONVERT(Date, '17/04/2014' , 103))
    OR 
    (Convert(Date, PHD.FromDate) = CONVERT(Date, '17/04/2014' , 103))
 

to this:

WHERE (PHD.FromDate <= CONVERT(DateTime, '17/04/2014 23:59:59' , 103) 
    AND PHD.ToDate>=CONVERT(DateTime, '17/04/2014 00:00:00' , 103))
 

Because the fields 'FromDate' and 'ToDate' are datetime values, you better compare them to datetime values.

P.S. Maybe i got the time wrong in this last query, but that's because i still dont understand what you are actually trying to select.... ;)

[tasawer]

Perfect... after my own solution, I thought I was the king :)