Doubt on date in SQL

Navigation
1

I'm setting up a database for a store to learn SQL. My goal is to have a movie return date. For this, I created the table with a column named "return" to store when the movie will return to the 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 you select 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 out.

    
asked by anonymous 21.01.2016 / 16:30

4 answers

1

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

  

When you select 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 out.

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

So, to properly run your command, leave it like this: 

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

To insert the date correctly has two important details, first leave it in single quotes and convert the date entered by the user into the bank format.

    
21.01.2016 / 16:41
0

To save the date in MySQL, it must be formatted with the standard 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 must be enclosed in quotation marks. 

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.

    
21.01.2016 / 16:49
-2

The question here is just the use of the wrong date pattern, which would be 'yyyy-mm-aaa' and in quotes, because it's a string.

  • yyyy - > The year represented by 4 digits
  • mm - > The month represented by 2 digits
  • dd - > The day represented by 2 digits
25.07.2018 / 16:26
Problem with AutoMapper relationship many to many C # Npgsql - Problem with Entity Framework 6