CODEWITHSUNDEEP
mysqlsqlisnull
Saffron
Saffron

Reputation: 682

What causes a Date to be <null> while specifically selecting the NOT NULL fields?

In one of my MYSQL databases, I have done the following query :

SELECT * FROM mytable WHERE date_column < 1971-01-01 AND date_column IS NOT NULL;

This returns about 3000 items where the date is shown as NULL in the mysql command line prompt, and in the IntelliJ IDEA mysql database editor.

The column has a DATE type.

The database is a copy of a prod database environment that has been running for a couple years.

How can a record end up in that state ? What is that state ? How can I fix it properly ?

PS: The < 1971/01/01 clause was just to filter the correct dates out of the query. If I just query on the IS NOT NULL clause, I still get these weird dates of course.

Upvotes: 2

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

I am surprised this works. First, date constants should be surrounded by single quotes:

SELECT *
FROM mytable
WHERE date_column < '1971-01-01' AND date_column IS NOT NULL;

Second, this cannot return a NULL value for date_column. This suggests one of two things:

  • date_col is actually another type, such as varchar and you are seeing 'NULL' the string, not NULL the value.
  • another column is NULL.

Upvotes: 3

Related Questions