使用当前日期作为列的默认值

在 SqlServer 中，是否有办法将列的默认值设置为 DateTime.Now？

例如:

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

台词是:

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

应该插入一行，Date 值应该是当前日期。

258469 次浏览
小开
最佳答案

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

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

You can use:

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

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

小开 
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
)
小开

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

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

enter image description here

小开

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());
小开

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) enter image description here

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

code already provided above

3) View the data BEFORE applying the change. enter image description here

4) View the data AFTER applying the change. enter image description here

小开

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

小开

Solution that seems to me much easier to remember :

alter table table_name add column date datetime default current_timestamp

提交答案