26
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?
26
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?
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:
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;
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" ), ...
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" ) ;
%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.
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.
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.– rray
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.– user6026
Ready, put what I use, Mysql.
– ptkato