#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    117
    Rep Power
    11

    MySql Date default.


    Hi I have looked around on the internet for having a default date on table creation. One loon sys NOW()

    MySQL say can't have functions but there manual gives no clear examples just 1000 of lines of text with links to more lines. Eventualy I get to

    http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

    This (a bit lower down) says

    CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

    But even this does not work

    How do I do this and why is it not in MySQL online manual

    Code:
    DROP TABLE IF EXISTS `EventPictures`;
    CREATE TABLE IF NOT EXISTS `EventPictures` 
    (
      `id` int(11) NOT NULL auto_increment,
      `Name` varchar(20) default NULL,
      `SName` varchar(20) default NULL,
      `Title` varchar(15) default NULL,
      `Description` varchar(120) default NULL,
      `Year` varchar(4) default NULL,
      `Event` varchar(30) default NULL,
      `Category` varchar(30) default NULL,
      `OrganisationID` varchar(32) default NULL,
      `UserID` varchar(32) default NULL,
      `Date` datetime DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    http://www.w3schools.com/sql/func_curdate.asp

    Code:
    CREATE TABLE Orders
     (
     OrderId int NOT NULL,
     ProductName varchar(50) NOT NULL,
     OrderDate datetime NOT NULL DEFAULT CURDATE(),
     PRIMARY KEY (OrderId)
     )
    NOT A CHANCE

    Last edited by Otuatail; June 14th, 2013 at 03:34 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    compare this --

    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

    with this --

    `Date` datetime DEFAULT CURRENT_TIMESTAMP

    see the difference?

    also, the w3schools example is for microsoft sql server, and by the way, the w3schools site is crap
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    117
    Rep Power
    11
    MySQL said:
    #1067 - Invalid default value for 'StartDate'

    DROP TABLE IF EXISTS `EventPictures`;
    CREATE TABLE IF NOT EXISTS `EventPictures`
    (
    `id` int(11) NOT NULL auto_increment,
    `Name` varchar(20) default NULL,
    `SName` varchar(20) default NULL,
    `Title` varchar(15) default NULL,
    `Description` varchar(120) default NULL,
    `Year` varchar(4) default NULL,
    `Event` varchar(30) default NULL,
    `Category` varchar(30) default NULL,
    `OrganisationID` varchar(32) default NULL,
    `UserID` varchar(32) default NULL,
    `StartDate` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    --
    -- Dumping data for table `EventPictures`
    --

    INSERT INTO `EventPictures` VALUES
    ('', 'L_IMGP0843.jpg', 'S_IMGP0843.jpg', '', '', '', '', '1','77563c2c31a43025df8d4ea66c7f66c9','1f3d1e9ddee44e22f53ed92a472433c0',''),
    ('', 'L_DSC05265.jpg', 'S_DSC05265.jpg', '', '', '', '', '1','77563c2c31a43025df8d4ea66c7f66c9','5b179d9cfa6fd0484f389bce215220e9',''),
    ('', 'L_DSC04452.jpg', 'S_DSC04452.jpg', '', '', '', '', '1','77563c2c31a43025df8d4ea66c7f66c9','511f5afa194cb4de176391662dbed143',''),
    ('', 'L_DSC04448.jpg', 'S_DSC04448.jpg', '', '', '', '', '1','77563c2c31a43025df8d4ea66c7f66c9','41d1b7b0bbca647fb8d1ee0d9cac2ad4',''),
    ('', 'L_DSC04427.jpg', 'S_DSC04427.jpg', '', '', '', '', '1','77563c2c31a43025df8d4ea66c7f66c9','3e31326147743421aa17bf6c94f414e6',''),
    ('', 'L_DSC04424.jpg', 'S_DSC04424.jpg', '', '', '', '', '1','77563c2c31a43025df8d4ea66c7f66c9','f9f7cc0e84668436eba72007c560f3b2','');

    also, the w3schools example is for microsoft sql server, and by the way, the w3schools site is crap

    The section was for MySQL but I do agree site is crap
    Last edited by Otuatail; June 14th, 2013 at 06:04 AM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    i guess i wasn't clear enough

    you can use DEFAULT CURRENT_TIMESTAMP only for a timestamp column, not for a datetime column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    117
    Rep Power
    11
    I realise that but I have spent 6 hours trying every single permutation and using every example on the internet. I have found lots of people out there all having problems with this issue. I have sent a support ticket to my host provider and another to MySQL/Oracle in USA (who are 7 hours behind) in Hawaii

    What is the correct syntax for this one pleas put me out of my missery.

    `StartDate` datetime DEFAULT CURRENT_TIMESTAMP,
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Otuatail
    What is the correct syntax for this one pleas put me out of my missery.

    `StartDate` datetime DEFAULT CURRENT_TIMESTAMP,
    the correct syntax is to remove the default --

    `StartDate` datetime

    i know it is difficult to accept, but there you go -- no columns (except timestamp) can use a function as a default
    Originally Posted by da manual
    10.1.4. Data Type Default Values
    The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.
    Last edited by r937; June 14th, 2013 at 06:19 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    117
    Rep Power
    11
    And this takes me full circle to where I was 6 hours ago

    `EndDate` datetime,

    Results are 6 rows with EndDate = 0000-00-00 00:00:00
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    you might want to consider actually populating a datetime value in your INSERT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo