SEO

Sorry about double post, I am having problems with my ISP.

I have the following query:

SELECT *
FROM Sight_Hearing_Help
WHERE 'type_help' = "Eye Exam & Glasses"
AND 'board_action_date' BETWEEN "07-01-2007" AND "12-31-2007"
LIMIT 0 , 60;

Returns empty row every time. The board_action_date is a varchar field. Not
a date field. I have also tried using form 2007-07-01.

Additional information:
Â* SUSE 10.2, MySQL 5.0.26-14

Any help would be appreciated!
--
Russ
Registered Linux user #441463

Oh boy.. having the date stored as a varchar in that particular format will be profoundly problematic. You might want to store it YYYY-MM-DD or the SQL BETWEEN will mangle the expected return results.

Does it work (return a non-empty result-set) when you omit the LIMIT clause?

Does it work (return a non-empty result-set) when you omit the board_action_date BETWEEN comparator clause?

Tim...


-----Original Message-----
From: russbucket [mailto:russbucket@nwi.net]
Sent: Wednesday, October 10, 2007 11:06 AM
To: mysql@lists.mysql.com
Subject: Query not returning Data

Sorry about double post, I am having problems with my ISP.

I have the following query:

SELECT *
FROM Sight_Hearing_Help
WHERE 'type_help' = "Eye Exam & Glasses"
AND 'board_action_date' BETWEEN "07-01-2007" AND "12-31-2007"
LIMIT 0 , 60;

Returns empty row every time. The board_action_date is a varchar field. Not
a date field. I have also tried using form 2007-07-01.

Additional information:
* SUSE 10.2, MySQL 5.0.26-14

Any help would be appreciated!
--
Russ
Registered Linux user #441463

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=tlittle@tgrnet.com

russbucket wrote:
> Sorry about double post, I am having problems with my ISP.
>
> I have the following query:
>
> SELECT *
> FROM Sight_Hearing_Help
> WHERE 'type_help' = "Eye Exam & Glasses"
> AND 'board_action_date' BETWEEN "07-01-2007" AND "12-31-2007"
> LIMIT 0 , 60;
>
> Returns empty row every time. The board_action_date is a varchar field. Not
> a date field. I have also tried using form 2007-07-01.

If board_action_date is a varchar not a date then I don't see how you
can expect between to work.

You could use the STR_TO_DATE[1] function on board_action_date in the
query but this is a very bad idea and will lead to extremely slow
queries. Your best option is to either alter board_action_date to a date
field, or add a new field for it.

-Stut

[1]http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

--
http://stut.net/

> Sorry about double post, I am having problems with my ISP.
>
> I have the following query:
>
> SELECT *
> FROM Sight_Hearing_Help
> WHERE 'type_help' = "Eye Exam & Glasses"
> AND 'board_action_date' BETWEEN "07-01-2007" AND "12-31-2007"
> LIMIT 0 , 60;
>
> Returns empty row every time. The board_action_date is a varchar field.
Not
> a date field. I have also tried using form 2007-07-01.
>
> Additional information:
> SUSE 10.2, MySQL 5.0.26-14
>
> Any help would be appreciated!

It could be me, but aren't you compareing a string to a string?

'type_help' (with normal single quotes) is a string value.

"Eye Exam & Glasses" can be a string value as well in MySQL, although you
should always use single quotes.

Oh, and a reminder: to solve your strange date-format problems, just store a
date in a DATE-type column.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

That is a string comparison, so they will never be equal. You don't
have to put quotes around field names unless you are using reserved
words, which you shouldn't. If you do use "quotes" around field
names, you need to use `backticks`.


On Oct 10, 2007, at 1:15 PM, Martijn Tonies wrote:

>
>
>> Sorry about double post, I am having problems with my ISP.
>>
>> I have the following query:
>>
>> SELECT *
>> FROM Sight_Hearing_Help
>> WHERE 'type_help' = "Eye Exam & Glasses"
>> AND 'board_action_date' BETWEEN "07-01-2007" AND "12-31-2007"
>> LIMIT 0 , 60;
>>
>> Returns empty row every time. The board_action_date is a varchar
>> field.
> Not
>> a date field. I have also tried using form 2007-07-01.
>>
>> Additional information:
>> SUSE 10.2, MySQL 5.0.26-14
>>
>> Any help would be appreciated!
>
> It could be me, but aren't you compareing a string to a string?
>
> 'type_help' (with normal single quotes) is a string value.
>
> "Eye Exam & Glasses" can be a string value as well in MySQL,
> although you
> should always use single quotes.
>
> Oh, and a reminder: to solve your strange date-format problems,
> just store a
> date in a DATE-type column.
>
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=brenttech@gmail.com
>