#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2005
    Posts
    628
    Rep Power
    147

    Default value for DATETIME field


    Hello all,

    I am trying to install a WordPress blog and the install script is failing as several of the tables contain a declaration like:

    Code:
    my_field datetime NOT NULL default '0000-00-00 00:00:00'
    I am getting the message "Invalid default value for 'my_field'".

    Is there is a setting I can change to allow for this declaration, bad as it may be?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2006
    Posts
    331
    Rep Power
    46
    The supported range for a datetime column is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

    So you should set your default to `1000-01-01 00:00:00` and it should work for you.

    For more details on the datetime type, please see the following section in the mysql manual:

    http://dev.mysql.com/doc/refman/5.0/en/datetime.html
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2005
    Posts
    628
    Rep Power
    147
    Yes but if you insert an invalid datetime value into a datetime field, the value is implicitly set to '0000-00-00 00:00:00' unless you have strict mode on. I think that is what the people at WordPress are trying to accomplish. Can you set this as a default?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2006
    Posts
    331
    Rep Power
    46
    Yes, you can change the SQL MODE and it will allow this.

    If you have TRADITIONAL enabled, then you'll get the warning/error as you have it. However, if you had slightly less restrictions, then this would be allowable.

    For instance, if you had the following sql_modes set (which provides some checking/constraints), then it will work:

    STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

    Note there is also a ALLOW_INVALID_DATES sql mode which you can combine with strict modes to allow these zeros in the dates. However, I was unable to get this to work with only it and TRADITIONAL, but it should work with other strict modes enabled (at least accoding to the manual).

    http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html

    Hope this helps.

IMN logo majestic logo threadwatch logo seochat tools logo