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

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.