[RESOLVED] Selecting records between two dates

blackbox

Hi all,

I am trying to write a query that returns all the records between two dates including the start date and the end date.

This is what I have so far:

SELECT * FROM Rose_Audit WHERE  (LastAudit BETWEEN '".$_SESSION['FromDate']."' AND '".$_SESSION['ToDate']."') AND Complete = 1

The issue I have is it only works returning the dates between and not the start or end date.

Can anyone see where I am going wrong.

Many thanks in advance for your time.

NogDog

My guess is that LastAudit has a time component in the DB (is a DATETIME or TIMESTAMP type)? If so, you might want to cast LastAudit to a DATE if your FromDate and ToDate values are just dates, e.g.

. . . WHERE DATE(LastAudit) BETWEEN . . .

PS: Not sure what impact that would have on performance, though -- i.e. does it force a complete table scan so that it can convert the timestamp or whatever to a date?

dalecosp

That should work; another possibility might be to add a time (23:59:59) to the last date...

blackbox

NogDog;11054343 wrote:
My guess is that LastAudit has a time component in the DB (is a DATETIME or TIMESTAMP type)? If so, you might want to cast LastAudit to a DATE if your FromDate and ToDate values are just dates, e.g.
. . . WHERE DATE(LastAudit) BETWEEN . . .
PS: Not sure what impact that would have on performance, though -- i.e. does it force a complete table scan so that it can convert the timestamp or whatever to a date?

Hi Nogdog, The "LastAudit" is a "date" type not a "datetime". This is why I can't understand why the query does not work.

I have attached images of the field design and the content.

[ATTACH]5305[/ATTACH][ATTACH]5307[/ATTACH]

LastAudit.png LastAudit_design.png

NogDog

Before we go much further, is my assumption that this is MySQL is correct? If not, a different DBMS might possibly have different implementations/restrictions. A little poking around in the MySQL documentation suggests when comparing dates, it's a good idea to explicitly convert any strings being used, so you might want to do something like:

... LastAudit BETWEEN DATE('2016-03-01') AND DATE('2016-03-31') ...

-- or to be really sure:

... DATE(LastAudit) BETWEEN DATE('2016-03-01') AND DATE('2016-03-31') ...

blackbox

NogDog;11054355 wrote:
Before we go much further, is my assumption that this is MySQL is correct? If not, a different DBMS might possibly have different implementations/restrictions. A little poking around in the MySQL documentation suggests when comparing dates, it's a good idea to explicitly convert any strings being used, so you might want to do something like:
... LastAudit BETWEEN DATE('2016-03-01') AND DATE('2016-03-31') ...

-- or to be really sure:

... DATE(LastAudit) BETWEEN DATE('2016-03-01') AND DATE('2016-03-31') ...

Hi Nogdog,

I used "... DATE(LastAudit) BETWEEN DATE('2016-03-01') AND DATE('2016-03-31') ..." and that produced the correct result.

What can I say, Nogdog to the rescue again. Many thanks for your time.

This was blowing my head apart.

Cheers.

NogDog

Cool...it's always gratifying to learn something new as part of trying to help someone else. 🙂