Current date as the default value of a date field

[sharmilah]

Hi all

It's me again. I now want to specify the current date in the format '26/06/2007' ie date/month/year as the default value in my mysql database. Which implies that by default the value of my date field in the database should be the current date. How can I do this in the Mysql database itself, so that each time my application is called the current date appears by default.

Thanks in advance for your replies

[sumaabey]

echo $d=date("d/m/Y");

[sharmilah]

Is it possible to specify the date format (e.g 26/06/2007) in the mysql database itself instead of converting it via php.

Another question : I want also to specify the format of a numeric field (999,999,999,999) in the database itself so that all numbers input get this format.

Thanks again

[sumaabey]

select date_format(test_date,'%d/%m/%Y') as test_date from test_table

this query will display the format that you need

do you want this?

[pbmods]

Heya, sharmilah.

Is it possible to specify the date format (e.g 26/06/2007) in the mysql database itself instead of converting it via php.

Yes. But it hasn't been implemented yet.

SHOW VARIABLES LIKE 'date%';

Another question : I want also to specify the format of a numeric field (999,999,999,999) in the database itself so that all numbers input get this format.

Can't specify a format for numeric types, but you can FORMAT() them.

[Motoma]

Is it possible to specify the date format (e.g 26/06/2007) in the mysql database itself instead of converting it via php.

Another question : I want also to specify the format of a numeric field (999,999,999,999) in the database itself so that all numbers input get this format.

Thanks again

To your first question, change your insert statement to use NOW() as the value of the date.

[Atli]

It is not possible (as far as I can tell) to use functions as a default values for fields in MySQl, so you will have to include this in your INSERT statement.
You can use the NOW() function in the Insert statement, which will insert the current date into the field.

If you don't like that you could create a procedure to do this, but thats a little much trouble for a simple thing like this, in my opinion.

[pbmods]

The one exception (I just discovered this today) is that you can use CURRENT_TIMESTAMP as the default value for the timestamp type.

No, you can't use CURRENT_DATE as the default value for a date type. I don't know why.

http://dev.mysql.com/doc/refman/5.0/...-defaults.html