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.

you're trying to use a function inside table creation? `DEFAULT()` isn't a function it's an "option". http://dev.mysql.com/doc/refman/5.7/en/create-table.html — Funk Forty Niner, Oct 20 '16 at 18:04
As a side-note, is `GETDATE()` a function in MySQL? Isn't it `NOW()`? — Tyler Roper, Oct 20 '16 at 18:05
`GETDATE()` isn't even a MySQL function, it's used in MSSQL https://msdn.microsoft.com/en-us/library/ms188383.aspx - which RDBMS are you really using? or are you not paying attention to commenters here? — Funk Forty Niner, Oct 20 '16 at 18:09
okay so that identifies the problem but doesn't explain how to fix it — aloeveraking24, Oct 20 '16 at 18:11
that guy gave you an answer that you're right; it didn't explain it where I made a comment about it. So... question solved or not? and which RDBMS are you using here? — Funk Forty Niner, Oct 20 '16 at 18:11
um no? it didn't. as i said, DateCreated DATETIME COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP gave me 'Invalid default value for 'DateCreated'' — aloeveraking24, Oct 20 '16 at 18:12
well he's the one with all those upvotes so I'll take a back seat on this one, and you didn't tell us which RDBMS you're using. — Funk Forty Niner, Oct 20 '16 at 18:14
@aloeveraking24 what version of MySql are you using? Prior to MySql 5.6.5 you cannot do a default of `CURRENT_TIMESTAMP` unless the column type is `TIMESTAMP` — Tyler Roper, Oct 20 '16 at 18:21
that is a very good question that I do not know the answer to! let me investigate. unless you know how I can find out. Using phpMyAdmin SQL Dump version 4.5.3.1 whatever this means — aloeveraking24, Oct 20 '16 at 18:25

Waldson Patricio · Accepted Answer · 2016-10-20T18:21:03.383

2

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.

edited Oct 20 '16 at 18:21

answered Oct 20 '16 at 18:04

Waldson Patricio

1,489
13
17

Why should they "try"? Unless the OP doesn't care for it. Personally, an explanation would be more beneficial for all. – Funk Forty Niner Oct 20 '16 at 18:06
`DateCreated` DATETIME COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP – aloeveraking24 Oct 20 '16 at 18:07
oops `DateCreated` DATETIME COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP gave me 'Invalid default value for 'DateCreated'' – aloeveraking24 Oct 20 '16 at 18:08
Answer updated with explanation. – Waldson Patricio Oct 20 '16 at 18:21

trying to create automatic timestamp for new records

1 Answers1