1. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    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?
  2. #2
  3. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    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).]
  4. #3
  5. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    that SQL statement doesn't work - it produces a default of 0000-00-00 00:00:00
  6. #4
  7. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    Sorry... I tested this out with phpMyAdmin and phpMyAdmin automatically defaults the date to the current date.

  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Rep Power
    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...
    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:

    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 10:04 PM.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    buggers, please always check da manual before posting

    as late as version 5, it says
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Similar Threads

  1. current date and current date + 7 days
    By rjstephens in forum Perl Programming
    Replies: 2
    Last Post: January 28th, 2004, 05:47 AM
  2. Multi-pass Query?
    By lennyg in forum MySQL Help
    Replies: 21
    Last Post: October 5th, 2003, 10:35 PM
  3. mySQL Values
    By XSilverSwordX in forum MySQL Help
    Replies: 8
    Last Post: September 25th, 2003, 08:56 AM
  4. mySQL and Win2k aborted connection crash
    By SDFiero in forum MySQL Help
    Replies: 3
    Last Post: September 28th, 2002, 10:29 PM
  5. default to current date
    By wifpwcs in forum MySQL Help
    Replies: 2
    Last Post: February 12th, 2002, 01:34 PM

IMN logo majestic logo threadwatch logo seochat tools logo