-2

In my one of the MySQL tables have one column cancelled_dts that is of type DateTime, which accepts null value and default set as "0000-00-00 00:00:00"

When I execute a query like this "SELECT * FROM tablename where cancelled_dts IS NULL" it's returning the rows which contain "0000-00-00 00:00:00"

I tried using the following query on the same database

select if('0000-00-00 00:00:00' != '0000-00-00 00:00:00', 'Not Same', 'Same'),
if('0000-00-00 00:00:00' IS NULL, 'NULL', 'NOT NULL'),
if('0000-00-00 00:00:00' IS NOT NULL, 'NOT NULL', 'NULL')

to check the behaviour and it gave output as "Same", "NOT NULL" and "NOT NULL" for the statements.

Abhijeet K
  • 137
  • 1
  • 2
  • 8

1 Answers1

0

'0000-00-00 00:00:00' is invalid DateTime value in mysql, DateTime.MinValue is 0001-01-01 00:00:00, but the minimum supported value for a DATETIME column is 1000-01-01 00:00:00 In many versions of MySQL Server

Rateb Habbab
  • 1,739
  • 8
  • 13
  • that is false, you can set this when you allow NULL in your sql mode – nbk Sep 01 '21 at 15:09
  • Please check https://dev.mysql.com/doc/refman/8.0/en/datetime.html before. It may allow you to add this value according to the GUI you are using to deal with mysql. So it may convert the '0000-....' value to null before store it to the table. – Rateb Habbab Sep 01 '21 at 15:13
  • I don't need to check https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime – nbk Sep 01 '21 at 15:17
  • the data is already there from legacy database – Abhijeet K Sep 01 '21 at 15:26