Error in database workbench, birth date, error 0000-00-00

Question

Error in database workbench, birth date, error 0000-00-00

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

    create table pessoas(
    id int  not null  auto_increment,
    nome varchar(50) not null,
    nascimento date,
    sexo enum('M','F'),
    peso decimal(5,2),
    altura decimal(2,2),
    nacionalidade varchar(20) default 'Brasil',
    primary key (id)
)default charset = utf8;

When I run

insert into pessoas(id,nome,nascimento,sexo,peso,altura,nacionalidade)
values
(DEFAULT,'JOAO PAULO', 2018-02-25,'M',90,1.68, DEFAULT);

is giving this result:

JOAO PAULO 0000-00-00 M 90.00 0.99 Brazil

You're showing this error:

1 31 19:37:13 insert into people (id, name, birth, sex, weight, height, nationality)
   values    (DEFAULT, 'JOAO PAULO', 2018-02-25, 'M', 90,1,68, DEFAULT)
  1 row (s) affected, 2 warning (s):
   1264 Out of range value adjusted for column 'birth' at row 1
   1264 Out of range value adjusted for column 'height' at row 1 0.015 sec

Where am I going wrong?

mysql

asked by anonymous 20.02.2018 / 23:58

2 answers

In pandas, how to merge two dataframes, but only a two-column stretch? How to pause and continue after an event?

score 2 · Answer 1

In addition to the missing quotation marks when entering the date, you are also creating a field ( altura ) with a maximum of 2 numbers, with 2 being precision (stays after the period).

Mathematically speaking, this is only possible with numbers between 0.01 and 0.99 . So it does not suit your case (height in meters). So you should use the structure as follows.

CREATE TABLE pessoas(
  id INT NOT NULL AUTO_INCREMENT,
  nome VARCHAR(50) NOT NULL,
  nascimento DATE,
  sexo ENUM('M', 'F'),
  peso DECIMAL(5, 2),

  /* Permite 3 números, sendo 2 deles de precisão (fica após a vírgula ou ponto) */
  altura DECIMAL(3, 2),

  nacionalidade VARCHAR(20) DEFAULT 'Brasil',
  PRIMARY KEY(id)
) DEFAULT CHARSET = utf8;

Or you can update the table.

ALTER TABLE 'pessoas' CHANGE 'altura' 'altura' DECIMAL(3,2) NULL DEFAULT NULL;

Demo

score 0 · Answer 2

0

Insert it like this:

INSERT INTO pessoas (id, nome, nascimento, sexo, peso, altura, nacionalidade) VALUES (DEFAULT, 'JOAO PAULO', '2018-02-25', 'M', 90, 1.68, DEFAULT);

21.02.2018 / 00:08