How to Mysql Extract Zero Date Value

gheharukoh7gheharukoh7 Community Edition User

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.

Answers

  • moshegmosheg Vertica Employee Administrator
    edited October 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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file