Avatar of Richard Korts
Richard KortsFlag for United States of America

asked on 

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
MySQL Server

Avatar of undefined
Last Comment
Richard Korts
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of F P
F P
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Richard Korts
Richard Korts
Flag of United States of America image

ASKER

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'
Avatar of Richard Korts
Richard Korts
Flag of United States of America image

ASKER

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
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

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'
Avatar of Richard Korts
Richard Korts
Flag of United States of America image

ASKER

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.
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo