Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How does this data truncation possible?

I am trying to insert some datetime values and I came up with a non sense data truncation problem, and I have no idea how to solve and what is causing it. Hope someone helps!

I have the following table:

CREATE TABLE tb_cliente ( 
    id bigint unsigned NOT NULL AUTO_INCREMENT, 
    st_servidor tinyint(1) NOT NULL, 
    nm_nome varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    nm_nome_social varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cd_matricula varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    cd_siape varchar(255) CHARACTER SET utf8mb4 
            COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    dt_nascimento timestamp NOT NULL, 
    sexo varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL, 
    nacionalidade varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    ano_chegada_no_pais varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    naturalidade varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    uf_nascimento varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    estado_civil varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    sn_conjuge_servidor tinyint(1) DEFAULT NULL, 
    nm_conjuge varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nm_pai varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nm_mae varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    raca varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cor_dos_olhos varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cor_dos_cabelos varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    sn_alergico tinyint(1) DEFAULT NULL, 
    grau_de_instrucao varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    dt_conclusao timestamp NULL DEFAULT NULL, 
    altura decimal(8,2) DEFAULT NULL, 
    tipo_sanguineo varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    sn_doador_de_orgaos tinyint(1) DEFAULT NULL, 
    nome_anterior varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    endereco varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cep varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cpf varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    telefone varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    telefone_celular varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_institucional varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_externo varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_comunicados varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    endereco_correspondencia varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cep_correspondencia varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    created_at timestamp NULL DEFAULT NULL, 
    updated_at timestamp NULL DEFAULT NULL, 
    user_id bigint unsigned DEFAULT NULL, 
    PRIMARY KEY (id), 
    UNIQUE KEY tb_servidor_nr_cpf_unique (nr_cpf), 
    KEY tb_servidor_user_id_index (user_id), 
    CONSTRAINT tb_servidor_user_id_foreign FOREIGN KEY (user_id) 
        REFERENCES tb_users (id) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

When I try to insert timestamp using year 1948, I get this error:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

SQL Error [1292] [22001]: Data truncation: Incorrect datetime value: ‘1948-07-19 00:00:00’ for column ‘dt_nascimento’ at row 1

But when I try to insert timestamp using year 1999, it inserts successfully using the following query:

insert into tb_cliente (
            updated_at, created_at, st_servidor, 
            nm_nome, cd_matricula, dt_nascimento, 
            sexo, nacionalidade, uf_nascimento, 
            estado_civil, grau_de_instrucao, nr_cpf, 
            telefone, telefone_celular, email_institucional, email_externo) 
values ('2022-10-25 00:00:00', '2022-10-25 00:00:00', '1', 
        'User name 1', '000000', '1999-07-19 00:00:00','M', 
        '1', 'AC', 'CASADO','ENSINO' , '000000', '000000', '000000', 
        'USER@UOL.COM.BR', 'USER@UOL.COM.BR' )


Mysql Server 8.0

>Solution :

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-syntax.html says:

A timestamp. The range is ‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19 03:14:07.999999’ UTC.

In other words, ‘1948-07-19 00:00:00’ is more than 21 years earlier than the earliest date a TIMESTAMP can store.

You should use DATETIME if you want dates outside this range.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading