CODEWITHSUNDEEP
phpmysqldaterange
Toadums
Toadums

Reputation: 2812

MySQL query with date ranges returning no results

So I am doing this query from PHP, and here listerally the exact query string:

SELECT * FROM `pdem_timesheet`.`tblMasterTimesheets` WHERE 
`pdem_timesheet`.`tblMasterTimesheets`.`username` = 'pdem' AND 
`pdem_timesheet`.`tblMasterTimesheets`.`date` >= '2012-05-09' AND 
`pdem_timesheet`.`tblMasterTimesheets`.`date` <= '2012-05-15' ORDER BY
`pdem_timesheet`.`tblMasterTimesheets`.`date` ASC

It looks like it should be correct to me (more-or-less copying it from previous code I used that DOES work). But when I run the query, the results are empty.

If I change the query to not be a date range, but just a single day:

SELECT * FROM .... WHERE ...`date` = '2012-06-12' ....

it works just fine, returns the one result that it should.

I have tried using the between keyword:

SELECT * FROM ... WHERE ...`date` BETWEEN [start] [end]

but it still returns nothing...

Any ideas how to get this query to return a result?

===ANSWER===

When you go: 

var curr_date = now.getDate();
var curr_month = now.getMonth();
var curr_year = now.getFullYear();

it returns the month - 1 for some reason. So if now's month is 6, now.getMonth() will return 5...Just need to add 1 in the query (wish I saw this sooner)

Upvotes: 1

Views: 3270

Answers (3)

Mike Mackintosh
Mike Mackintosh

Reputation: 14237

Similar to Fahim Parkar, here is an example of your query working with the use of the BETWEEN syntax: http://sqlfiddle.com/#!2/0fcb5/4

It sounds like your user pdem does not exist.

Upvotes: 2

StuckAtWork
StuckAtWork

Reputation: 1633

Make sure that:

  1. There is in fact a result which should be returned when using your given criteria. Make sure the DD-MM-YYYY syntax is correct and make sure you know which month is which number (may is 05, june is 06)
  2. That the datatype of the column date is of a date type, not a generic text/varchar type. You cannot compare varchar with >= like that (not the way you want, at least. Only works on date types)

As Fahim said, your code is correct. It must be something within the table which is causing your issues.

Upvotes: 1

Fahim Parkar
Fahim Parkar

Reputation: 31627

Your query seems to be working for me.

See demo.

Query I have is

SELECT * FROM tblMasterTimesheets
WHERE 
username='pdem'
AND
date >= '2012-05-09' AND
date <= '2012-05-15'
ORDER BY date ASC

I assume, username is of type varchar and date is of type timestamp or datetime.

Upvotes: 2

Related Questions