Date Doubt in SQL

Asked

Viewed 83 times

1

I’m setting up a database for a video store to learn SQL. My goal is to have a return date of the film. For this, I created the table with a column named "return" to store when the movie will return to the video store, using the following format:

create table acao 
(
act_id int not null,
nome varchar(30) not null,
genero varchar(15) not null,
diretor varchar(30) not null,
classificacao char(2) not null,
disponibilidade char(1) not null,
retorno date    
);

To fill in, I used the following command:

insert into acao (act_id, nome, diretor, classificacao, disponibilidade, retorno)
values (4, "The Mask of Zorro", "Martin Campbell", 12, "A", 23/05/2016)

When selecting to see if it worked, the date is returned as 0000-00-00. I understand that the format is YYYY-MM-DD, but I would like to know why it does not take the values I filled in.

mysql sql

3 answers

1

To enter the date correctly has two important details, first leave it in simple quotes and convert the date typed by the user in the database format.

  • Thank you very much!

  • @Mateusbinatti is using some specific language?

  • i use Mysql Workbench

1


I answered the question in the comments. Just to leave the definitive answer, put here.

When selecting to see if it worked, the date is returned as 0000-00-00. I understand that the format is YYYY-MM-DD, but I would like to know why it does not take the values I filled in.

To insert a date with SQL you need to use the correct formatting, as you said yourself: YYYY-MM-DD.

So, to properly run your command, let it so:

insert into acao (act_id, nome, diretor, classificacao, disponibilidade, retorno)
values (4, "The Mask of Zorro", "Martin Campbell", 12, "A", "2016-05-23")

  • Perfect, Eduardo. Thank you very much indeed!

0

To save date in Mysql, it must be formatted by default ISO 8601

Scope: yyyy-mm-dd

  • yyyy -> The year represented by 4 digits
  • mm -> The month represented by 2 digits
  • dd -> The day represented by 2 digits

Example: 2016-01-22

In the SQL query, the date is treated as a string, so it needs to be enclosed in quotes.

insert into acao (act_id, nome, diretor, classificacao, disponibilidade, retorno)
values (4, "The Mask of Zorro", "Martin Campbell", 12, "A", '2016-05-23')

Mysql also accepts the date in other formats. However, it is recommended to maintain the ISO 8601 standard.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.