MySQL Query

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
Richard KortsBusiness Owner / Chief DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
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.
F PCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
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
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Richard KortsBusiness Owner / Chief DeveloperAuthor 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 DeveloperAuthor 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 ProblemsCommented:
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 DeveloperAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.