How to insert date and datetime into a table in Mysql?

Asked

Viewed 119,390 times

26

Erro na inserção de dados do tipo date e datetime

The big problem is that after the insert, all values that have date, are as zeroes and in the format yyyy-dd-mm and not dd/mm/yyyy, as it was inserted initially. How can I insert these values into the formatting I wish?

mysql date

  • SQL, mysql? sql server? the date should be recorded as yyyy-mm-dd in select vc use some function that the database provides to format as needed.

  • 1

    Yes, your bank format is yyyy-mm-dd the configuration and you do not respect it, so he put zeros, thinking it is Mysql such behavior, but, it would be nice to put the language (if you have used) and the name of the bank.

  • 2

    Ready, put what I use, Mysql.

2 answers

37


You cannot directly enter data in any format you want in Mysql for fields datetime and timestamp. The format Mysql uses is the ISO standard: AAAA-MM-DD HH:MM:SS and some variants in the same order.

However, this does not prevent the user from converting the formats at the time of data input and output - remembering that anyway, the storage format will remain the same in DB.

See how to do this conversion using the Mysql functions themselves:


To view dates as dd/mm/aaaa in the SELECT:

With the function DATE_FORMAT( data, formato ) you can convert a date into a formatted string.

Example for your table:

SELECT usr_idusuario, DATE_FORMAT( evn_dtevento, "%d/%m/%Y" ) AS data_evento FROM evn_evento;


How to use a string dd/mm/aaaa by making the INSERT:

The function that does the inverse of the above example is STR_TO_DATE( string, formato ).

Applying to your table:

INSERT INTO usr_idusuario SET evn_dtevento = STR_TO_DATE( "31/05/2014", "%m/%d/%Y" ), ...


How to use a string dd/mm/aaaa in the clause WHERE:

SELECT campos FROM tabela WHERE dia = STR_TO_DATE( "31/05/2014", "%m/%d/%Y" );

Or for a break:

SELECT campos FROM tabela
WHERE  dia BETWEEN
           STR_TO_DATE( "01/05/2014", "%m/%d/%Y" ) AND
           STR_TO_DATE( "31/10/2014", "%m/%d/%Y" ) ;


Placeholders of the most common formats for our locale:

%d   Dia (00..31)
%e   Dia (0..31)
%m   Mês (00..12)
%y   Ano (dois dígitos)
%Y   Ano (quatro dígitos)
%%   Caractere %
%H   Horas (00..23)
%i   Minutos (00..59)
%s   Segundos (00..59)
%T   Hora completa ( 24 horas, formato hh:mm:ss)

The formats are listed in full on mysql manual

  • In a clause WHERE should I wear DATE_FORMAT or STR_TO_DATE?

  • @Patrick depends on the type of field and what you want to do. I usually always work with the date in the original Mysql format, so I don’t use any function. I only control the display on the screen in the application.

0

The default format is YYYY-MM-DD, to extract the information should be dealt with in the application or in the consultation.

Creating the field with default value in the bank, or passing now() in the INSERT.

Thus it will be completed with the current value.

  • 1

    Use the now() only solves the problem when the date is the current one. If it is a retroactive or future date, for example, this would not be a viable option. It may be interesting to give more context and explain what other alternatives are and/or how to deal with the date format suggested by the questioner, if you have something else to add or improve beyond the accepted answer. ;)

Browser other questions tagged mysql date

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