trying to create automatic timestamp for new records

Question

here's the table:

   CREATE TABLE `obits` (

  `pid` int(11) NOT NULL,

  `surname` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `firstname` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `middlename` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `maiden` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `presuf` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `birth` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `death` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `obitdate` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `obitsource` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `sourcepage` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `obittext` text COLLATE utf8_unicode_ci,

  `DateCreated` DATETIME COLLATE utf8_unicode_ci NOT NULL DEFAULT(GETDATE())

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

here is the error I'm getting: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(GETDATE())
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci' at line 14 (line 14 is the datecreated line)

I probably missed something really simple but for the life of me I don't know what. Thank you for any/all help. This table was fine before I tried to re-create it with the datecreated line.

Answer 1

You can't use a function to fill default values in MySql, except CURRENT_TIMESTAMP for TIMESTAMP columns. Prior to version 5.6.5, you must use TIMESTAMP.

DateCreated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

After version 5.6.5, you are able to use DATETIME columns with CURRENT_TIMESTAMP as default value.

trying to create automatic timestamp for new records

Sign up