aloeveraking24 aloeveraking24 - 1 month ago 5
MySQL Question

trying to create automatic timestamp for new records

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

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.

Comments