Add default value of datetime field in SQL Server to a timestamp

Question

I ve got a table that collects forms submitted from our website  but for some reason  when they created the table  they didn t put a timestamp in the table  I want it to enter the exact date and time that the record was entered   I know it s in there somewhere  but I can t seem to find how to set the default value  like in Access  you use getNow   or Now    but I don t know where to put it

User · Answer

Disallow Nulls on the column and set a default on the column of getdate      Deal with any existing NULLs   UPDATE YourTable SET created date GETDATE     Or some sentinel value                                                   19000101  maybe    WHERE created date IS NULL     Disallow NULLs   ALTER TABLE YourTable ALTER COLUMN created date DATE NOT NULL    Add default constraint   ALTER TABLE YourTable ADD CONSTRAINT     DF YourTable created date DEFAULT GETDATE   FOR created date

User · Answer

This worked for me  I am using SQL Developer with Oracle DB   ALTER TABLE YOUR TABLE   ADD Date Created TIMESTAMP  DEFAULT CURRENT TIMESTAMP NOT NULL

User · Answer

While the marked answer is correct with      ALTER TABLE YourTable ADD CONSTRAINT DF YourTable DEFAULT GETDATE   FOR YourColumn   You should always be aware of timezones when adding default datetime values in to a column   Say for example  this datetime value is designed to indicate when a member joined a website and you want it to be displayed back to the user  GETDATE   will give you the server time so could show discrepancies if the user is in a different locale to the server   If you expect to deal with international users  it is better in some cases to use GETUTCDATE    which      Returns the current database system timestamp as a datetime value  The database time zone offset is not included  This value represents the current UTC time  Coordinated Universal Time   This value is derived from the operating system of the computer on which the instance of SQL Server is running    ALTER TABLE YourTable ADD CONSTRAINT DF YourTable DEFAULT GETUTCDATE   FOR YourColumn   When retrieving the values  the front end application website should transform this value from UTC time to the locale culture of the user requesting it

User · Answer

For modifying an existing column in an existing table   ALTER TABLE YourTable ADD CONSTRAINT DF YourTable DEFAULT GETDATE   FOR YourColumn

User · Answer

This works for me     ALTER TABLE  accounts    ADD  user registered  DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP

User · Answer

In SQLPlus while creating a table it is be like as   SQL  create table Test     Test ID number not null     Test Date date default sysdate not null      SQL  insert into Test id  values  1     Test ID Test Date        1 08-MAR-19

User · Answer

This also works   CREATE TABLE Example      created datetime default GETDATE        Or   ALTER TABLE EXAMPLE ADD created datetime default GETDATE

User · Answer

To make it simpler to follow  I will summarize the above answers   Let s say the table is called Customer it has 4 columns less or more     you want to add a new column to the table where every time when there is insert    then that column keeps a record of the time the event happened   Solution   add a new column  let s say timepurchase is the new column  to the table with data type datetime   Then run the following alter   ALTER TABLE Customer ADD CONSTRAINT DF Customer DEFAULT GETDATE   FOR timePurchase

User · Answer

This can also be done through the SSMS GUI    Put your table in design view  Right click on table in object explorer- Design  Add a column to the table  or click on the column you want to update if it already exists  In Column Properties  enter  getdate    in Default Value or     Binding field as pictured below

User · Answer

Let s say you create a database table for a registration system   IF OBJECT ID  dbo registration demo    U   IS NOT NULL    DROP TABLE dbo registration demo    CREATE TABLE dbo registration demo       id INT IDENTITY PRIMARY KEY      name NVARCHAR 8       Now a couple people register   INSERT INTO dbo registration demo  name  VALUES       John     Jane     Jeff      Then you realize you need a timestamp for when they registered   If this app is limited to a geographically localized region  then you can use the local server time with GETDATE    Otherwise you should heed Tanner s consideration for the global audience with GETUTCDATE   for the default value   Add the column with a default value in one statement like this answer   ALTER TABLE dbo registration demo ADD time registered DATETIME DEFAULT GETUTCDATE      Let s get another registrant and see what the data looks like   INSERT INTO dbo registration demo  name  VALUES       Julia     SELECT   FROM dbo registration demo     id    name    time registered 1     John    NULL 2     Jane    NULL 3     Jeff    NULL 4     Julia   2016-06-21 14 32 57 767

User · Answer

In that table in SQL Server  specify the default value of that column to be CURRENT TIMESTAMP  The datatype of that column may be datetime or datetime2  e g  Create Table Student     Name varchar 50     DateOfAddmission datetime default CURRENT TIMESTAMP

User · Answer

The syntax for this when creating a new table is   CREATE TABLE MyTable       MYTableID INT IDENTITY 1 1        CreateDate DATETIME NOT NULL CONSTRAINT DF MyTable CreateDate GETDATE DEFAULT GETDATE

[sql-server] Add default value of datetime field in SQL Server to a timestamp

Examples related to sql-server

Examples related to datetime