why doesn't this SQL give the result I want?

[Bobby_S]

hi guys,

I don't understand what I'm doing wrong here:

I have a table bookings and there is 1 app_date 2005-08-03 I want to fetch. (this is checked, it's there :)) Format is date 0000-00-00

This query shows that one row correct:

SELECT b.id, DATE_FORMAT(b.app_date, '%d-%m-%Y')
FROM zzzbookings b
WHERE b.id >= 0
AND DATE_FORMAT(b.app_date, '%d-%m-%Y') BETWEEN '02-04-2005' AND '05-08-2005'


However, this one doesn't:

SELECT b.id, DATE_FORMAT(b.app_date, '%d-%m-%Y')
FROM zzzbookings b
WHERE b.id >= 0
AND DATE_FORMAT(b.app_date, '%d-%m-%Y') BETWEEN '04-04-2005' AND '05-08-2005'


This last one doesn't have a result... :confused: :confused: :confused:
What's wrong here??

thx in advance.

[tripwater]

Could you please post more code?

Reason I ask is there are a number of things that can cause a query to not yeild a row. Also, why are you using alias on one table?

I am also confused by your date format example. Are you looking to get a result of d-m-y of 8-3-2005 to show up between the ranges of 04-04-2005' AND 05-08-2005? Because it won't. And if that is the case, your first example should not yeild a result either. So I am confused. Please clarify.

[Bobby_S]

Hi there,

no, no variables are being reset, and the alias is there because this snippet is just a part from a bigger query. I forgot to clean that part. I only tried to keep the part of the query that is related to my problem.

The result should be 3-8-2005 (so July; and not 8-3-2005 as you mentioned there).
3 July is between 4 April and 5 July ...

I've been thinking this over again and maybe the BETWEEN isn't working as I assumed it would ...
maybe
DATE_FORMAT(b.app_date, '%d-%m-%Y') BETWEEN
only works right with you put the year first, then month, then day??
Like
DATE_FORMAT(b.app_date, '%Y-%m-%d') BETWEEN

Any ideas?

[NogDog]

Not sure, but I think this might be more correct:

SELECT b.id, DATE_FORMAT(b.app_date, '%d-%m-%Y')
FROM zzzbookings b
WHERE b.id >= 0
AND b.app_date BETWEEN '2005-04-04' AND '2005-08-05'

[Bobby_S]

Yes I think that will work. However, the date input has a format d-m-Y like '04-04-2005'.
Is there a PHP function that converts this to '2005-04-04' instead of doing this on MySQL level? some equivalent of DATE_FORMAT. That would solve my issue.

[NogDog]

This should do the trick:

list($d, $m, $y) = explode("-", $date);
$date_reversed = sprintf("%s-%s-%s", $y, $m, $d);

[Bobby_S]

explode ... never heard of.
I'll try that this evening. Thanks for your help guys!

[BeachSide]

explode just splits a string up with the needle that you give it (e.g. NogDog used '-') and turns it into an array


// so for example this...
$string = '03-08-2005';

// or this...
$string = 'Ray-Bob-Jill';

// Split em up...
$newString = explode('-', $string);

/*
becomes in essence...

this for the first example
$newString[0] => '03'
$newString[1] => '08'
$newString[2] => '2005'

or this for the second example
$newString[0] => 'Ray'
$newString[1] => 'Bob'
$newString[2] => 'Jill'
*/


Which becomes quite useful for splitting up long strings like this sentence!


<?php
$string = 'Which becomes quite useful for splitting up long strings like this sentence!';

$newString = explode(' ', $string);

var_dump($newString);

?>

and the output looks like this...

array(12) {
[0]=> string(5) "Which"
[1]=> string(7) "becomes"
[2]=> string(5) "quite"
[3]=> string(6) "useful"
[4]=> string(3) "for"
[5]=> string(9) "splitting"
[6]=> string(2) "up"
[7]=> string(4) "long"
[8]=> string(7) "strings"
[9]=> string(4) "like"
[10]=> string(4) "this"
[11]=> string(9) "sentence!"
}

[Stephen Philbin]

Also note that the string by which you explode the original string, will be removed.

[Bobby_S]

Yes, it works! Interesting function that is. :)
Thanks for the help!