mySQL - BETWEEN not working :(

[Kold]

Hello!

I am trying to get the fields from a database where the mySQL timestamp is between set criteria.

I have tried a number of different things, but here is my current syntax;

SELECT ID, Date_Entered
FROM tblTesting
WHERE  `User_ID`='2' AND `Date_Entered` 
BETWEEN  '20040600000000' AND  '200409%%%%%%%%'

The problem i have with this is that it returns everything... including 20040512163412, which is before the month 06!!!

Any ideas why it doesnt work?
Thanks!

[mrhappiness]

try 20041001000000 instead of 200409%%%%%%%%

[Kold]

Nope, it still returns 05's 🙁

I have even tried brackets... but still doesnt work.

WHERE (
`User_ID` = '2'
) AND (
`Date_Entered` 
BETWEEN '20040700000000' AND '20041001000000'
)

[tsinka]

Hi,

the problem might be 20040700000000. This isn't a valid date because they day is 00. I think, MySQL replaces invalid timestamp values by 0 (I hope this is true, I read something like that in the manuals some time ago). So your query will be internally handled as BETWEEN '0' AND '20041001000000'.

Try to use 20040701000000 instead.

Thomas

[rachel2004]

If you use mktime(), you have options for which date components you wish to specify (YYYY-MM-DD or YYYY-MM):

<?php
// use PHP mktime() to set your timestamps
$begin_range = mktime(0,0,0,6,0,2004);
$end_range = mktime(0,0,0,7,6,2004);

// use MySQL function UNIX_TIMESTAMP()
$sql = "SELECT ID,
               UNIX_TIMESTAMP(Date_Entered)
        FROM tblTesting
        WHERE UNIX_TIMESTAMP(Date_Entered)
        BETWEEN '$begin_range' 
        AND '$end_range'";

$result = mysql_query($sql, $link_id)
    or die("Couldn't execute query: ".mysql_error());

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    // do something with query results
}
?>

[Kold]

Thank you!

tsinka - I had tried that, but it didnt work before... perhaps i put the 1 in to wrong place lol!

rachel2004 - After a little fidling around with your code, it now works perfectly 😃

Thanks everyone!