Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to Mysql Extract Zero Date Value

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 Employee
    edited October 19
    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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.