#1
  1. No Profile Picture
    dchrist
    Guest
    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
    netman
    Guest
    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
    dchrist
    Guest
    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
    netman
    Guest
    Devshed Newbie (0 - 499 posts)
    Sorry... I tested this out with phpMyAdmin and phpMyAdmin automatically defaults the date to the current date.

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

    Join Date
    Dec 2007
    Posts
    45
    Rep Power
    37
    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.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    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, 04:47 AM
  2. Multi-pass Query?
    By lennyg in forum MySQL Help
    Replies: 21
    Last Post: October 5th, 2003, 09:35 PM
  3. mySQL Values
    By XSilverSwordX in forum MySQL Help
    Replies: 8
    Last Post: September 25th, 2003, 07:56 AM
  4. mySQL and Win2k aborted connection crash
    By SDFiero in forum MySQL Help
    Replies: 3
    Last Post: September 28th, 2002, 09:29 PM
  5. default to current date
    By wifpwcs in forum MySQL Help
    Replies: 2
    Last Post: February 12th, 2002, 12:34 PM

IMN logo majestic logo threadwatch logo seochat tools logo