Richard Doe Richard Doe - 3 years ago 267
MySQL Question

MySQL Incorrect DateTime Value 2005

I have a web app that collect a simple poll data from my friends. I wanna run a SELECT query to get names based on when the date and month they run put a vote on a poll.

So this is my queries:

SELECT * FROM tbl_votedata
WHERE votersname = "Jack Ryan"
AND targetname = "Bill Murray"
AND votetype = "Friends"
AND MONTH(timestamp) = MONTH(2017-10-02)
AND YEAR(timestamp) = YEAR(2017-10-02)

Unfortunately, when I run it, it returns error like this (I'm using HeidiSQL):

enter image description here

Wasn't MySQL datetime value is 'YYYY-MM-DD HH:MM:S'? So, it should be run OK right? Since my query only select the year and month only...

So what is wrong exactly?

Answer Source

Date constants should be quoted, something like this:

FROM tbl_votedata 
WHERE votersname = 'Jack Ryan'
  AND targetname = 'Bill Murray'
  AND votetype = 'Friends'
  AND MONTH(timestamp) = MONTH('2017-10-02') 
  AND YEAR(timestamp) = YEAR('2017-10-02')

It was evaluating the 20107 - 10 - 2 expression and used the result as the argument to the function, which was not the correct data type...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download