From 244d8924d17897293df0ca652cb1361f946bb431 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Mon, 1 Mar 2021 11:14:32 +0100
Subject: [PATCH] Bug 27821: Make sanitize_zero_date support datetime and
 timestamp

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
(cherry picked from commit 3e3803ee20dc3dfc3d21e5cafa7a76159c8d4aba)
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>
(cherry picked from commit 3652f652d260bafc8669205d59866db792e56f47)

Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
---
 installer/data/mysql/updatedatabase.pl | 22 +++++++++++++++++-----
 1 file changed, 17 insertions(+), 5 deletions(-)

diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 81dfd42093..7e9e114c65 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -22691,11 +22691,23 @@ sub CheckVersion {
 
 sub sanitize_zero_date {
     my ( $table_name, $column_name ) = @_;
-    $dbh->do(qq|
-        UPDATE $table_name
-        SET $column_name = NULL
-        WHERE CAST($column_name AS CHAR(10)) = '0000-00-00';
-    |);
+
+    my (undef, $datatype) = $dbh->selectrow_array(qq|
+        SHOW COLUMNS FROM $table_name WHERE Field = ?|, undef, $column_name);
+
+    if ( $datatype eq 'date' ) {
+        $dbh->do(qq|
+            UPDATE $table_name
+            SET $column_name = NULL
+            WHERE CAST($column_name AS CHAR(10)) = '0000-00-00';
+        |);
+    } else {
+        $dbh->do(qq|
+            UPDATE $table_name
+            SET $column_name = NULL
+            WHERE CAST($column_name AS CHAR(19)) = '0000-00-00 00:00:00';
+        |);
+    }
 }
 
 exit;
-- 
2.39.5