set DEFAULT to current date

Dev Shed Forums Sponsor:

September 13th, 1999, 02:09 PM

dchrist

Guest

Posts: n/a
Time spent in forums:
Reputation Power:

is there a way to set the default for a column to be the current date, without making that column a timestamp? i'm trying to provide the current date as the starting point for a column that will ultimately be changed. it seems that if mysql would accept multiple select statements, i should be able to do it with the following syntax:

ALTER table paintings ALTER column creation_date SET DEFAULT (select current_date());

any suggestions?

September 13th, 1999, 06:59 PM

netman

Guest

Posts: n/a
Time spent in forums:
Reputation Power:

Use the now() function as your default:

ALTER TABLE paintings CHANGE creation_date creation_date DATETIME DEFAULT 'now()' not null';

now() gives the current date & time.

[This message has been edited by netman (edited 09-13-99).]

September 13th, 1999, 08:11 PM

dchrist

Guest

Posts: n/a
Time spent in forums:
Reputation Power:

that SQL statement doesn't work - it produces a default of 0000-00-00 00:00:00

September 14th, 1999, 04:39 PM

netman

Guest

Posts: n/a
Time spent in forums:
Reputation Power:

Sorry... I tested this out with phpMyAdmin and phpMyAdmin automatically defaults the date to the current date.

Sorry.

February 12th, 2010, 05:01 PM

buggers

Contributing User

Join Date: Dec 2007

Posts: 45

Time spent in forums: 14 h 42 m 29 sec
Reputation Power: 36

hi,
i believe i am having a similar problem... i need to set my default value to a function as well, but pma seems to have none of this. is there perhaps any way to have it done via the mysql console?

i need to set the default value of a timestamp field to CONVERT_TZ(CURRENT_TIMESTAMP, 'SYSTEM', '+2:00'), the following select statement works in PMA to change the timezone...

Code:

SELECT CONVERT_TZ(CURRENT_TIMESTAMP, 'SYSTEM', '+2:00') AS timestamp_SAST;

if i uncheck the timestamp mark, and insert the CONVERT_TZ function as used above into the default field in phpMyAdmin, it sets the default time to 0000-00-00 00:00:00 as yours does... :/ i guess one cannot add functions to default values, yet this page says you can:
http://www.modwest.com/help/kb6-256.html

Comments on this post

requinix disagrees: the thread is more than 9 years old. expected more from soneone with 43 posts

Last edited by buggers : February 12th, 2010 at 09:04 PM.

February 12th, 2010, 06:46 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140

buggers, please always check da manual before posting

as late as version 5, it says

Quote:

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression.

you're welcome

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

set DEFAULT to current date

Developer Shed Advertisers and Affiliates