Select from date format

[awebbdesigner]

Hi all,

 

I have done a script that works fine on a single basis...

 

SELECT * FROM company

WHERE so <> 0

AND so_active = 'yes'

AND DATE_FORMAT(so_start_date, '%e') = '18'

ORDER BY company_name ASC

 

The problem I have is when I try to do a range...

 

SELECT * FROM company

WHERE so <> 0

AND so_active = 'yes'

AND DATE_FORMAT(so_start_date, '%e') BETWEEN '5' AND '26'

ORDER BY company_name ASC

 

It should return at least 1 result as I am looking for '18', any help would be much appreciated.

 

Many Thanks.

 

[bh]

Hi,

 

Simply remove the single quotes.

 

SELECT * FROM company 
WHERE so <> 0 
AND so_active = 'yes' 
AND DATE_FORMAT(so_start_date, '%e') BETWEEN 5 AND 26
ORDER BY company_name ASC

[awebbdesigner]

Sorry, I just tried that and it didn't return any results. i also shortened the query for testing purposes to...

SELECT * from company WHERE DATE_FORMAT(so_start_date, '%e') BETWEEN 4 AND 20 ORDER BY company_name ASC

 

Any help would be much appreciated.

[bh]

The query its ok, so you should check your datas.

 

SELECT DATE_FORMAT(so_start_date, '%e') from company;

[awebbdesigner]

Many thanks for your swift response.

 

I have just ran a few tests and seem to have unusual results, when i try...

SELECT * from company WHERE so <> 0 AND so_active = 'yes' AND DATE_FORMAT(so_start_date, '%e') BETWEEN 5 AND 22 ORDER BY company_name ASC

 

It doesn't work, however...

SELECT * from company WHERE so <> 0 AND so_active = 'yes' AND DATE_FORMAT(so_start_date, '%e') BETWEEN 16 AND 20 ORDER BY company_name ASC

 

Works fine (the company returned has a %e = 18) so it seems like MYSQL doesn't like the between statement if its between range is too large.

 

The so_start_date column is a standard 'datetime' type (YYYY-m-d H:i:s).

 

I am a bit puzzled but I think we are nearly there.

 

 

 

[litebearer]

a guess (datetime is YYYY-MM-DD) soooo try putting a zero in front of single digit numbers ie '05'

[awebbdesigner]

I have tried using %d and %e both with no success. I tried putting a zero in front with also no success.

 

Strange thing is I changed the value to 2010-08-09 00:00:00 Making the value I was looking for to '9'

I then did a between test of the following...

1 - 9 = worked.

8 - 9 = worked.

9 - 10 = failed.

 

The only thing I can think of is MYSQL doesn't like using between date formats from 1 digit to 2 digits. The MYSQL version is 3.23 so I wondered whether that was the issue (currently exploring)

[awebbdesigner]

Just ran a test on a more recent server and found it worked straight away.

I will mark this topic as solved as it looks like it was to do with an old mysql version rather than the code itself.

 

Many thanks to all who have helped.