1

I was trying to find customers born in month of december from a table customers using this query : 

SELECT *
FROM customers
WHERE date_of_birth LIKE '____-12-%' ;

But it throws an error : ER_WRONG_VALUE: Incorrect DATE value: '____-12-%'

The type of date_of_birth value is DATE that is it's defined in table as date_of_birth DATE,

Snapshot of the error that i got :

Here is the image link

What exactly is wrong here? I remember earlier ones i have used this and it worked fine but now (may be due to new updates) it's not working. Doesn't even seem to be because of MySQL Strict Mode. I can't get what exactly is causing problem.

Also please don't suggest me alternatives i already have those like using MONTH() works fine :

SELECT * 
FROM customers 
WHERE MONTH(date_of_birth) = 12;
James Z
  • 12,209
  • 10
  • 24
  • 44
Sunny Nehra
  • 11
  • 5
  • 1
    What is the data type of `date_of_birth`? Explain why `month()` is not the right solution. – Gordon Linoff Jul 06 '19 at 15:25
  • LIKE should **not** be used on DATE or DATETIME datatypes, it is meant to be used on string datatypes only like VARCHAR / CHAR and TEXT... – Raymond Nijland Jul 06 '19 at 15:28
  • @GordonLinoff i am not talking about the solution brother. Do try to get me. Earlier in old days i used to use same query and it used to work fine. Type of date_of_birth is DATE – Sunny Nehra Jul 06 '19 at 15:49
  • @SunnyNehra . . . You used to do it wrong. Now you know how to do it right. – Gordon Linoff Jul 06 '19 at 15:54
  • What software are you using to run the query? – forpas Jul 06 '19 at 16:16
  • @forpas i am using popsql (latest version). I know it should be working fine but it's giving error to me and i am using four underscores (because year has 4 digits). The type of date_of_birth value is DATE. – Sunny Nehra Jul 06 '19 at 16:21
  • Ok then you must use 4 underscores and a dash before 12: https://www.db-fiddle.com/f/vbkkCeFeidnzgRyvKSin8N/2 – forpas Jul 06 '19 at 16:26
  • I don't know if popsql supports all weird features of MySql like this, because comparing a date with like is not sql standard. – forpas Jul 06 '19 at 16:36
  • @forpas pardon ! i used 4 underscores and a dash also (i tried many queries) but did not work. I updated the question also as four underscores with a dash is in fact right one (though not working). And earlier i used it in popsql (old version) it used to work then. – Sunny Nehra Jul 06 '19 at 16:40
  • So the problem is with popsql because as you can see in the fiddle in my other comment, the code should work. – forpas Jul 06 '19 at 16:41

2 Answers2

2

Do not use string functions on date data types. Period. Mixing data types just leads to problems in SQL. Using like on a date requires converting the date to a string and the exact format depends on the locality and settings of the server.

If your data of birth is a string, fix it:

alter table modify column date_of_birth date;

Then the correct way is to use the syntax which you -- for some unknown reason -- specifically do not want to use:

where month(date_of_birth) = 12

If date_of_birth is stored as a string and for some reason cannot be stored using a native format, then you are depending on the unspecified format of the string. Presumably, you want something like this:

where date_of_birth like '%-12-%'  -- assuming date_of_birth is a string

assuming that the month is in the middle of the date string, surrounded by hyphens, and the day is either at the beginning or end.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • But on this website https://www.db-fiddle.com/ (as commented by @forpas on the question) it works well. And in popsql (i am using popsql) in older versions it worked perfect (exactly same tables and same code) but now in new version it's showing that error. That's what confusing me. – Sunny Nehra Jul 06 '19 at 19:33
  • @SunnyNehra . . . As I said in an earlier comment, it depends on the server settings. The right way is to use functions for the appropriate data type. – Gordon Linoff Jul 06 '19 at 19:42
  • +1. Select `cast(DateCol as varchar(100))` and you'll see a totally different output, i.e reformats the date. – Ismail Sep 30 '21 at 03:15
0

try to where month(date_or_birth)=12 i did not try yet..but there month function in sql

yokana
  • 189
  • 3
  • 12