need to add a DATE column in a table

prabhakaran66 · #1 (**permalink**) 11-15-11, 08:29

i am using mysql-v5.1 and i want to add a DATE column in a table,

my requirement is,whenever i do insert a new row that column should update with current date and the date format should be like 'yyyymmdd' or 'yyyy-mm-dd' not like 'yyyy-mm-dd hh:mm:ss' .

so kindly tell me how to add a such a column.

r937 · #2 (**permalink**) 11-15-11, 08:52

you are in luck

Code:

ALTER TABLE foo
ADD COLUMN bar TIMESTAMP

here's the explanation from da manual --

Quote:

A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. Variations on automatic initialization and update properties are described in Section 10.3.1.1, “TIMESTAMP Properties”.

prabhakaran66 · #3 (**permalink**) 11-16-11, 05:02

if i add TIMESTAMP ,it will make column as "2011-11-16 15:13:00" but i need column as "2011-11-16"(only date).

r937 · #4 (**permalink**) 11-16-11, 05:11

Quote:

Originally Posted by prabhakaran66

if i add TIMESTAMP ,it will make column as "2011-11-16 15:13:00" but i need column as "2011-11-16"(only date).

you can use DATE_FORMAT function to show only the date

prabhakaran66 · #5 (**permalink**) 11-16-11, 05:35

is it possible to define that in column description.

r937 · #6 (**permalink**) 11-16-11, 05:47

no it isn't

your alternative is to use a DATE column, but you will have to remember to update the value yourself every time you insert or update a row

Internet Services

Web Development

Digital Marketing

Consumer Tech