CODEWITHSUNDEEP
sql-serverdatetime
stephmoreland
stephmoreland

Reputation: 4163

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

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.

Upvotes: 369

Views: 1147102

Answers (12)

Sanjay
Sanjay

Reputation: 1665

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
);

Upvotes: 155

dsixnine
dsixnine

Reputation: 111

This works for me...

ALTER TABLE [accounts] 
 ADD [user_registered] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;

Upvotes: 11

Tony L.
Tony L.

Reputation: 19466

This can also be done through the SSMS GUI.

  1. Put your table in design view (Right click on table in object explorer->Design)
  2. Add a column to the table (or click on the column you want to update if it already exists)
  3. In Column Properties, enter (getdate()) in Default Value or Binding field as pictured below

Image of table in design view

Upvotes: 195

Usama Zafar
Usama Zafar

Reputation: 97

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

Upvotes: 0

not_selfish
not_selfish

Reputation: 19

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453756

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

Upvotes: 25

JavaGeek
JavaGeek

Reputation: 581

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;

Upvotes: 4

Jeff Puckett
Jeff Puckett

Reputation: 41021

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

Upvotes: 1

Keoma Borges
Keoma Borges

Reputation: 694

This also works:

CREATE TABLE Example(
...
created datetime default GETDATE()
);

Or:

ALTER TABLE EXAMPLE ADD created datetime default GETDATE();

Upvotes: 5

Tanner
Tanner

Reputation: 22753

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.

Upvotes: 36

David Sopko
David Sopko

Reputation: 5663

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()
)

Upvotes: 11

TheQ
TheQ

Reputation: 7017

For modifying an existing column in an existing table:

ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn

Upvotes: 440

Related Questions