• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

MySQL ignoring where clause?

I have written a simple sub-query that pulls out the year from a datetime field in a MySQL DB. The WHERE clause checks that the year is >= 2009.  The Query looks like:

SELECT YEAR(EndTime)
FROM `call`
WHERE YEAR(EndTime) >= '2009'

I have tried this in both MySQL Workbench and in SQL Server Management Studio (using a linked server).  The result set contains results that are not valid given the where clause (malformed dates exist in the data set where the year is 5130, etc.).  

Thanks in advance!
0
selliott80919
Asked:
selliott80919
  • 2
  • 2
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
Try removing the single quotes around 2009 so it's used as a number and not a string.
0
 
cyberkiwiCommented:
Hi there,

>> malformed dates exist in the data set where the year is 5130
That explains it.
If you have data in your 'call' table with dates in the year 5130, surely that's greater than 2009!!
Data entry error.
To list them, use this

SELECT EndTime
FROM `call`
WHERE YEAR(EndTime) >= 2009
0
 
cyberkiwiCommented:
Or if you want to ignore the crazy times, use this

SELECT YEAR(EndTime)
FROM `call`
WHERE YEAR(EndTime) between 2009 and 2010
0
 
NerdsOfTechTechnology ScientistCommented:
Simplified
SELECT YEAR(EndTime)
FROM `call`
WHERE YEAR(EndTime) >= 2009

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
make sure that your EndTime column data is correctly formatted too :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now