0

I wanted to add a time column to my SQL table that automatically sets the time when a user is created. I currently have ALTER TABLE users ADD COLUMN timeCreated datetime NOT NULL DEFAULT(GETDATE());, but I get the error "Function or expression 'GETDATE()' cannot be used in the DEFAULT clause of timeCreated". I'm not sure where I am going wrong here.

astentx
  • 6,393
  • 2
  • 16
  • 25
random person
  • 92
  • 6
  • 2
    What DB is used? Please add a tag, but only for the specific database. – Paul T. Oct 30 '21 at 22:14
  • @PaulT. I use phpmyadmin. – random person Oct 30 '21 at 22:20
  • Phpmyadmin is a tool, not a DBMS. DBMS is MySQL – astentx Oct 30 '21 at 22:22
  • 2
    Does this answer your question? [CURRENT\_DATE/CURDATE() not working as default DATE value](https://stackoverflow.com/questions/20461030/current-date-curdate-not-working-as-default-date-value) or [Automatically Initialize GETDATE() when inserting into MYSQL](https://stackoverflow.com/questions/21054028/automatically-initialize-getdate-when-inserting-into-mysql) – astentx Oct 30 '21 at 22:24

1 Answers1

0

You should modify your code to look like this

ALTER TABLE users ADD  CONSTRAINT DEFAULT_INSERTEDDATE 
DEFAULT GETDATE()  FOR timeCreated ;

Edited :- answered before adding MySQL tag

I think it should work on MySQL but I am not sure

Edited :- Or use the following

ALTER TABLE users ADD COLUMN timeCreated datetime NOT NULL DEFAULT GETDATE();
nbk
  • 45,398
  • 8
  • 30
  • 47
khaled Dehia
  • 821
  • 7
  • 13
  • How would I specify `NOT NULL`? Also, what does DEFAULT_INSERTEDDATE do? – random person Oct 30 '21 at 22:24
  • Just add not null to the constraints, if you already created the constraint you can creat a new one , but just for your information since it's default it won't be null because if the application send null value it will take the default value so it will never be null – khaled Dehia Oct 30 '21 at 22:28
  • The second option does not work. – random person Oct 30 '21 at 22:30
  • @randomperson *Doesn't work* means exactly nothing, because we cannot see your screen and guess what you call *doesn't work*. Please, post the error message you have or describe why the provided solution doesn't fit your needs. – astentx Oct 30 '21 at 22:32
  • Then alter the table and alter the column like this ALTER TABLE users ALTER COLUMN timeCreated datetime NOT NULL – khaled Dehia Oct 30 '21 at 22:34
  • Also keep in mind to make sure you don't have null data in your column before you alter it – khaled Dehia Oct 30 '21 at 22:35
  • Thanks for the help, I was able to solve it using the following code: `ALTER TABLE users ADD COLUMN timeCreated datetime DEFAULT (CURRENT_TIMESTAMP);` – random person Oct 30 '21 at 22:36