use current date as default value for a column

Question

Is there a way to set the default value of a column to DateTime.Now in Sql Server?

Example:

table Event
Id int (auto-increment) not null
Description nvarchar(50) not null
Date datetime not null

The line:

Insert into Event(Description) values('teste');

should insert a row and the Date value should be the current date.

This link may be useful too - http://blog.sqlauthority.com/2013/05/10/sql-server-adding-column-defaulting-to-current-datetime-in-table/ — Steam, Oct 26 '13 at 05:30

score 119 · Accepted Answer · edited Apr 14 '14 at 08:06

119

Add a default constraint with the GETDATE() function as value.

ALTER TABLE myTable 
  ADD CONSTRAINT CONSTRAINT_NAME
    DEFAULT GETDATE() FOR myColumn

edited Apr 14 '14 at 08:06

John Willemse

6,508
7
30
44

answered Apr 19 '11 at 14:42

Jason

4,387
5
29
39

9

In PostgreSQL it would be: "alter table myTable add column mydatecol date default now();" .. i only add this comment since the Sql-Server is not mentioned in the question's title. Might help a PostgreSQL user... – alfonx Jul 09 '11 at 12:12
1

This would be the form if the column already exists in the table, and it has no constraint to set a default value if the value is omitted in INSERT statements (or from CURSOR inserts). – TT. Jun 08 '13 at 11:34
The way this is written, it looks like it's missing the `CONSTRAINT` keyword. It should be `... ADD CONSTRAINT MY_CONSTRAINT_NAME ...` – Faust Apr 09 '14 at 11:20
1

Does `GETDATE()` give us the UTC time? – Zapnologica Nov 02 '16 at 07:10

score 27 · Answer 2 · edited Mar 28 '12 at 15:18

27

CREATE TABLE Orders(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE() // you can set default constraints while creating the table
)

edited Mar 28 '12 at 15:18

Barry Kaye

7,442
6
39
62

answered Apr 19 '11 at 14:54

Mayuresh

271
2
2

3

This form sets an unnamed constraint. It is best to name the constraint: `[...]OrderDate DATE CONSTRAINT DF_Orders_date DEFAULT GETDATE()[...]` – TT. Jun 08 '13 at 11:35

score 5 · Answer 3 · answered Apr 19 '11 at 14:43

5

You can use:

Insert into Event(Description,Date) values('teste', GETDATE());

Also, you can change your table so that 'Date' has a default, "GETDATE()"

answered Apr 19 '11 at 14:43

Blazes

4,575
2
21
29

score 3 · Answer 4 · edited Feb 10 '17 at 14:21

3

Select Table Column Name where you want to get default value of Current date

 ALTER TABLE 
 [dbo].[Table_Name]
 ADD  CONSTRAINT [Constraint_Name] 
 DEFAULT (getdate()) FOR [Column_Name]

Alter Table Query

Alter TABLE [dbo].[Table_Name](
    [PDate] [datetime] Default GetDate())

edited Feb 10 '17 at 14:21

Roman Marusyk

21,493
24
66
105

answered Dec 31 '15 at 16:12

Khalid

523
7
17

score 3 · Answer 5 · answered Jul 31 '18 at 20:15

I have also come across this need for my database project. I decided to share my findings here.

1) There is no way to a NOT NULL field without a default when data already exists (Can I add a not null column without DEFAULT value)

2) This topic has been addressed for a long time. Here is a 2008 question (Add a column with a default value to an existing table in SQL Server)

3) The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records IF no other value is specified. (https://www.w3schools.com/sql/sql_default.asp)

4) The Visual Studio Database Project that I use for development is really good about generating change scripts for you. This is the change script created for my DB promotion:

GO
PRINT N'Altering [dbo].[PROD_WHSE_ACTUAL]...';

GO
ALTER TABLE [dbo].[PROD_WHSE_ACTUAL]
    ADD [DATE] DATE DEFAULT getdate() NOT NULL;

-

Here are the steps I took to update my database using Visual Studio for development.

1) Add default value (Visual Studio SSDT: DB Project: table designer)

2) Use the Schema Comparison tool to generate the change script.

code already provided above

3) View the data BEFORE applying the change.

4) View the data AFTER applying the change.

Maher Ben Issa · Answer 6 · 2017-12-10T12:58:04.143

2

To use the current date as the default for a date column, you will need to:

1- open table designer

2- select the column

3- go to column proprerties

4- set the value of Default value or binding propriete To (getdate())

edited Dec 10 '17 at 12:58

answered Dec 10 '17 at 12:51

Maher Ben Issa

621
7
12

score 1 · Answer 7 · answered Apr 30 '21 at 10:13

1

Solution that seems to me much easier to remember :

alter table table_name add column date datetime default current_timestamp

answered Apr 30 '21 at 10:13

raphael.oester

104
9

tRuEsAtM · Answer 8 · 2018-04-05T22:48:26.893

0

Table creation Syntax can be like:

Create table api_key(api_key_id INT NOT NULL IDENTITY(1,1) 
PRIMARY KEY, date_added date DEFAULT 
GetDate());

Insertion query syntax can be like:

Insert into api_key values(GETDATE());

edited Apr 05 '18 at 22:48

answered Apr 05 '18 at 22:11

tRuEsAtM

3,607
5
35
66

score 0 · Answer 9 · answered Jul 08 '19 at 11:12

0

Right click on the table and click on Design,then click on column that you want to set default value.

Then in bottom of page in column properties set Default value or binding to : 'getdate()'

answered Jul 08 '19 at 11:12

Mohammad Reza Shahrestani

1,075
2
17
24

use current date as default value for a column

9 Answers9

Linked