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.

Beware of timezones: https://stackoverflow.com/a/31296917/57475 — Tanner, Feb 13 '19 at 10:16

score 427 · Accepted Answer · answered Feb 03 '11 at 15:56

427

For modifying an existing column in an existing table:

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

answered Feb 03 '11 at 15:56

TheQ

6,858
4
35
55

2

@TheQ - Thanks. I get that I change "YourTable" to my table name, but the part I don't understand is the Constraint. What is a constraint and would I modify your code to match the name of my table? – stephmoreland Feb 03 '11 at 15:59
@steph - `TheQ`'s answer assumes you are altering an existing column which I don't believe to be the case? See my answer for the code to add a new column with this default constraint. – Martin Smith Feb 03 '11 at 16:02
1

In order for a column to have a default value, it needs a "default constraint", and this command will add that. You can name the constraint whatever you like, Management Studio usually names them DF_TableName. – TheQ Feb 03 '11 at 16:03
1

@TheQ - That's perfect, I'll try it. You were right, I already have the field, but I wanted to apply the fix to it, not create a new one. Thanks for all your answers! – stephmoreland Feb 03 '11 at 16:06
This will however not update existing NULL's in the table, check @Martin's updated post for that :) – TheQ Feb 03 '11 at 16:12
12

If you want the UTC timestamp instead of local time, you can use `GETUTCDATE()` instead of `GETDATE()` – Cocowalla Jan 17 '13 at 11:59
`ALTER TABLE [YourTable] ADD CONSTRAINT [DF_YourTabe_YourColumn] DEFAULT (getdate()) FOR [YourColumn]` my 2 cent – zulucoda Nov 12 '13 at 11:28
I was not able to execute this on a field that was of type Timestamp, Changed to DateTime and worked fine. – DanO Jan 06 '15 at 16:41
SQL: Can you use `NOW()`? Works on *SQLyog*, which doesn't allow `getdate()` as it picks it as an error – Dennis Henry Dec 24 '16 at 08:16

Tony L. · Answer 2 · 2019-03-27T14:18:55.010

187

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

edited Mar 27 '19 at 14:18

answered May 08 '15 at 16:18

Tony L.

17,638
8
69
66

Good question. I would try `dateadd(minute, 10, GetDate())` to see if that works. – Tony L. Mar 08 '19 at 17:20
In sql server design view i tried it, it say value must match one of the items in list! so i made it on back end side – shareef Mar 09 '19 at 04:34
3

I tried this one but I get `0001-01-01T00:00:00` instead of the current date – Swaleh Matongwa Aug 25 '20 at 09:38

score 144 · Answer 3 · edited Sep 01 '20 at 11:30

144

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

edited Sep 01 '20 at 11:30

Ahmed Ashour

5,179
10
35
56

answered Feb 03 '11 at 15:53

Sanjay

1,555
2
10
6

1

This fails with the error message "Unable to convert between [B and TIMESTAMP]". It seems CURRENT_TIMESTAMP is a binary value, not a datetime. – Sindri Traustason Oct 18 '13 at 12:37
TIMESTAMP will give you an input like this : yyyy-mm-dd 00:00:00 UTC; and that can make the DB be so large; i think he meant only the date yyyy-mm-dd; and the default value for it can be set by clicking on "As Defined as" in the normal view of phpmyadmin – Apr 21 '16 at 09:32
@Amine, TIMESTAMP is a binary value, typically using 6-8 bytes. The string representation is just that, a representation, just like the database isn't storing integers as strings. (Unless you put the time into a VARCHAR column, and woe be those that try.) – SilverbackNet Aug 14 '18 at 01:41
5

I note that `CURRENT_TIMESTAMP` returns a `datetime` value in the computer's local time zone. It should be avoided. Instead use `SYSUTCDATETIME` which returns a `datetime2` in UTC. – Dai Jul 01 '19 at 20:34
Just to add to the comment from @Dai : `DateofAdmission datetime2(7) CONSTRAINT [DEFAULT_Student_DateOfAdmission] DEFAULT (SYSUTCDATETIME()) NOT NULL` – Josh Gallagher Aug 08 '23 at 11:23

score 34 · Answer 4 · edited May 23 '17 at 12:18

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.

Good advice. To add to that, however, Microsoft recommends that you use `DateTime2` over `DateTime`. As such you should use `SYSUTCDATETIME` to get the UTC date as it has better precision. If you need local server time then you should consider using `SYSDATETIMEOFFSET` to get the local time and store the offset. In this case use the `DateTimeOffset` type. — scottrudy, Nov 23 '20 at 18:14

score 24 · Answer 5 · edited Oct 02 '18 at 17:19

24

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

edited Oct 02 '18 at 17:19

Weasle

3
3

answered Feb 03 '11 at 15:51

Martin Smith

438,706
87
741
845

How would you alter that to change the existing field "created_date"? – stephmoreland Feb 03 '11 at 16:04
@steph - See Edit. My previous answer supposed that this was a new column. You will need to disallow NULLs for the default to work so how do you want pre-existing rows to be treated? – Martin Smith Feb 03 '11 at 16:08
1

Does a `DEFAULT GETDATE()` guarantee that a multi-row `INSERT` will have the same `DATE` for all inserted rows, or might the `GETDATE` be evaluated per row? And similarly for `SYSDATETIME()`? – John Rees Oct 30 '20 at 03:23

David Sopko · Answer 6 · 2013-11-19T22:01:10.650

11

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

edited Nov 19 '13 at 22:01

answered Mar 30 '12 at 14:45

David Sopko

5,263
2
38
42

It works equally well in an ALTER TABLE statement when adding a new column. – Captain Sensible Sep 07 '16 at 08:14

score 10 · Answer 7 · edited Jul 03 '19 at 20:41

10

This works for me...

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

edited Jul 03 '19 at 20:41

p.campbell

98,673
67
256
322

answered Nov 15 '17 at 22:14

dsixnine

101
1
5

score 5 · Answer 8 · answered Mar 01 '16 at 13:14

5

This also works:

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

Or:

ALTER TABLE EXAMPLE ADD created datetime default GETDATE();

answered Mar 01 '16 at 13:14

Keoma Borges

683
2
12
27

score 4 · Answer 9 · edited Aug 01 '18 at 13:51

4

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;

edited Aug 01 '18 at 13:51

Toby Speight

27,591
48
66
103

answered Aug 01 '18 at 13:34

JavaGeek

475
5
14

score 1 · Answer 10 · edited Dec 18 '18 at 13:52

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

score 1 · Answer 11 · edited May 23 '17 at 12:18

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

score 0 · Answer 12 · answered Mar 08 '19 at 18:31

0

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

answered Mar 08 '19 at 18:31

Usama Zafar

77
1
3

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

12 Answers12

Linked

Related