CODEWITHSUNDEEP
mysqlsqlphpmyadmin
user1464296
user1464296

Reputation:

SQL setting a Date Stamp

I'm in phpmyadmin right now and I am trying to set a field to stamp the current date when data is inserted.

Now I tried setting it to DATETIME type, but that includes time. I just want date. I also tried setting it to DATETIME with the default value to GETDATE() and saving but it threw an error:

#1067 - Invalid default value for 'upload_date'

Anyone know how to do this please?

Upvotes: 0

Views: 233

Answers (2)

João Silva
João Silva

Reputation: 91349

Unfortunately, you can't set the DEFAULT value to a function, it needs to be a constant value. One exception is CURRENT_TIMESTAMP, although it will also include the time part:

column_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP

However, you can use a TRIGGER to set the column's default value. For example:

CREATE TRIGGER trigger_name BEFORE INSERT ON your_table
  FOR EACH ROW SET NEW.date_column = DATE(NOW());

Also, use DATE() to truncate the TIME part of NOW().

Upvotes: 5

Najzero
Najzero

Reputation: 3202

A common error, I just throw the doc at you http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.1.5.5, “Automatic Initialization and Updating for TIMESTAMP”.

Also in the doc is the exception to use the current_timestamp - that requires your column to be a TIMESTAMP column.

Upvotes: 0

Related Questions