0

i have a column with the datatype DATETIME and im trying to get it to automatically insert the current date and time. i tried setting the value to NOW() but it gave the following error

ERROR 1292: 1292: Incorrect datetime value: 'NOW()' for column 'date' at row 1

does anyone know how this is accomplished? im using mysql workbench

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
  • It would probably be useful to see that actual statement you have written – Martin Jul 30 '19 at 15:19
  • set `DEFAULT CURRENT_TIMESTAMP` in the table definition and it's fixed – Lelio Faieta Jul 30 '19 at 15:19
  • 1
    Are you inserting `"NOW()"` with quotes? Maybe it's understood as a string where you really want NOW() as a function without the quotes. – M. Suleiman Jul 30 '19 at 15:23
  • Martin im using mysql workbench so im not writing the query. @lelio, i have already created the tables and its columns. i tried to alter the table via workbench and tried to set the column in question to "DEFAULT CURRENT_TIMESTAMP" but it gave an error. also didnt give an error code it just says it contains errors and cannot be accepted –  Jul 30 '19 at 15:29
  • Please show up your original code so I can try to help up you. – kokom Jul 30 '19 at 15:36
  • 1
    im using mysqlworkbench like i said i dont have any original code im using a gui –  Jul 30 '19 at 15:38

1 Answers1

0

NOW() should return the datetime correctly which should match the field type. As I mentioned in the comment, you might be inserting the value "NOW()" as a literal string, which isn't a valid datetime format. Make sure you don't have quotes around NOW().

M. Suleiman
  • 858
  • 4
  • 22
  • i didnt have quotes around NOW(). is the datatype i specified correct to use the now function? –  Jul 30 '19 at 15:35
  • @Cole-p in your question when you said "automatically", do you mean setting a default value? If this is what you mean, you can take a look here at this question because there's a slight dependency on the version you're using: https://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column – M. Suleiman Jul 30 '19 at 15:38
  • thanks, thats what lelio was saying but its not working for me. i have already created the table in mysql workbench so when i try to alter it using the gui it doesnt accept it. what would be the sql code to alter the column so i can try input it manually? –  Jul 30 '19 at 15:43
  • have this sorted now found the option in mysqlworkbench –  Jul 30 '19 at 15:48
  • @Cole-p make sure to remove the two paranthases `()` after `DATETIME` in Workbench like this: https://pasteboard.co/IqonETd.png – M. Suleiman Jul 30 '19 at 15:50