MySQL Query

Richard Korts
Richard Korts used Ask the Experts™
on
I have this query on a MySQL table

SELECT * from cvisitor where SUBSTRING(last_time,1,10) >= '2016-01-27' and SUBSTRING(last_time,1,10) <= '2016-01-28' and email not like '%@lakos.com' order by last_time

There are a number of rows where the email IS like @lakos.com. They are not excluded.

If I do the following, it works.

SELECT * from cvisitor where email not like '%@lakos.com'

Why does not the first work?  Do I need parens around date comparisons?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
I think that dates are not stored as strings but in there own special format.  Try...

SELECT SUBSTRING(last_time,1,10)

Open in new window


And see what you get.
Top Expert 2015
Commented:
Try BETWEEN like this:

SELECT *
FROM cvisitor
WHERE 
	last_time BETWEEN '2016-01-27' AND '2016-01-28' AND email NOT LIKE '%@lakos.com'
ORDER BY last_time

Open in new window


MYSQL: BETWEEN CONDITION
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
If last_time is a datetime field then
SELECT * 
  FROM cvisitor 
  WHERE
    DATE(last_time) BETWEEN  '2016-01-27' AND '2016-01-28' 
    AND email NOT LIKE '%@lakos.com' 
  ORDER BY DATE(last_time)

Open in new window


To extract the date portion of a date time string you can use the DATE function https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Richard KortsBusiness Owner / Chief Developer

Author

Commented:
To Dave Baldwin,

I have been using the dates as strings for years successfully in MySQL. The query is dynamically built in php based on values on an input form. The date part works perfectly. The issue is the like '%@lakos.com'
Richard KortsBusiness Owner / Chief Developer

Author

Commented:
To all,

Subsequent to this post I discovered that some of the email addresses have non-printable characters (I think just one) at the end. I discovered that by using php strlen and getting one more that the actual # of printable chars in some of the emails.

That would seem to explain why the like '%@lakos.com' fails because it should be like '%@lakos.com%' .

It does NOT explain why SELECT * from cvisitor where email not like '%@lakos.com' works

I haven't tried that yet, I think a simpler solution is when I loop through the result set, just use php to eliminate any that contain the string '@lakos.com' using php strpos.

Richard
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
While MySQL returns the results as strings, it does not store the dates that way.  Details matter.  To the point of this question, have you been using this function successfully before?

SUBSTRING(last_time,1,10) >= '2016-01-27'
Richard KortsBusiness Owner / Chief Developer

Author

Commented:
Dave,

No, but as I mentioned it works perfectly if I use ONLY the date selectivity part.

I recognize that details matter. Everything I do is about details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial