Jump to content

Select from date format


awebbdesigner

Recommended Posts

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

 

 

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.