Skip to main content
Question

How to Mysql Extract Zero Date Value

  • October 19, 2021
  • 1 reply
  • 5 views
gheharukoh7

I have a mysql table that has a '0000-00-00 00:00:00' and i'm trying to load a mysql dump using the copy command and getting the Date/time field value out of range: "0000-00-00 00:00:00". How would you suggest approaching this, is anyone aware of an option to mysqldump command or the vertica copy command that will allow this to be null, I was going to use the null option in the vertica copy command and use that string but i'm already using the null option for \N which the mysql uses for nulls in other data types.

    1 reply

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • October 19, 2021
    CREATE TABLE t0(f1 TIMESTAMP);

COPY t0 (
f1_filler FILLER VARCHAR(30), f1 AS CASE WHEN f1_filler = '0000-00-00 00:00:00' THEN NULL ELSE f1_filler::TIMESTAMP end
) from STDIN delimiter '|' NULL '' ABORT ON ERROR;
2021-11-26 00:00:00
0000-00-00 00:00:00
2022-01-10 00:00:00
\.

SELECT * FROM t0;
         f1
---------------------

 2021-11-26 00:00:00
 2022-01-10 00:00:00
(3 rows)

SELECT CASE WHEN f1 IS NULL THEN 'NULL' ELSE f1::varchar END
FROM t0;
         f1
---------------------
 NULL
 2021-11-26 00:00:00
 2022-01-10 00:00:00
(3 rows)
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement