MySQL convert DATETIME field 0000-00-00 00:00:00.000000 to DATE

Question

I have a table:

mysql> describe sk_users;
+------------------------------+--------------+------+-----+---------+----------------+
| Field                        | Type         | Null | Key | Default | Extra          |
+------------------------------+--------------+------+-----+---------+----------------+
| name                         | varchar(255) | NO   |     | NULL    |                |
| dst_date                     | datetime(6)  | YES  |     | NULL    |                |
+------------------------------+--------------+------+-----+---------+----------------+

and data:

mysql> select name, dst_date from sk_users;
+------+----------------------------+
| name | dst_date                   |
+------+----------------------------+
| a    | 2023-02-28 00:00:00.000000 |
| b    | 2016-06-01 00:00:00.000000 |
| c    | 0000-00-00 00:00:00.000000 |
+------+----------------------------+

I want to convert the 'dst_date' field from datetime to date, and when I'm trying to do it - I'm getting an error:

mysql> ALTER TABLE `sk_users` CHANGE `dst_date` `dst_date` DATE NULL;
ERROR 1292 (22007): Incorrect date value: '0000-00-00 00:00:00' for column 'dst_date' at row 3

I'm sorry, I can't figure out why I can't do this. Can you help me?

Thanks to juergen, I'm also tried to update the table to use NULL values instead, but it also gives me an error:

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'dst_date' at row 3

Answer 1

I've found a solution!

Steps:

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You can see NO_ZERO_IN_DATE, NO_ZERO_DATE. We need to temporary remove it.

mysql> SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

After that let's update our table to use NULL instead 0000-00-00 00:00:00.

update 
  sk_users
set 
  dst_date = NULL
where
  dst_date LIKE '0000-00-00%';

Then we can logout/login again (to restore our original sql_mode) and it works fine!

Answer 2

If you don't have a value you should actually use null. So why not update the table first and then change the table structure?

update sk_users
set dst_date = NULL
where dst_date = '0000-00-00 00:00:00'

Answer 3

Actually the convention is to null data that we don't have. So, instead filling the fields with '0000-00-00' just set it to NULL then you'll be able to change your table's structure more conveniently.

Here's a snippet how you can do it:

update 
  sk_users
set 
  dst_date = NULL
where
  dst_date = '0000-00-00 00:00:00';

Answer 4

There's a Date() function:

UPDATE sk_users set dst_date = date(dst_date);

It seems there's a bug in mysql forcing us to assign a sentinel to the date if it's null. So, the final answer becomes:

UPDATE sk_users set dst_date = now() where dst_date is null;UPDATE sk_users set dst_date = date(dst_date);

Hope it helps.