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

You can not directly insert data in any format you want in MySQL to datetime and timestamp fields. The format that 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 moment of entering and leaving the data - remembering that anyway the storage format will remain the same in the DB.

Here's how to do this conversion using the MySQL functions themselves:

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

With the DATE_FORMAT( data, formato ) function 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 when doing 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 WHERE clause:

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

Or for a range:

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 in the MySQL (en)

    

The default format is YYYY-MM-DD , to extract the information should be handled in the application or query.

Creating the default value field in the database, or passing now() to INSERT .

This will be completed with the current value.