CODEWITHSUNDEEP
sql-server
Samantha J T Star
Samantha J T Star

Reputation: 32778

How can I set the date of every row in a table to the current date?

I have this SQL Server table:

CREATE TABLE [dbo].[Synonym]
(
    [SynonymId] [int] NOT NULL,
    [WordFormId] [int] NOT NULL,
    [Ascii]  AS (ascii([Text])) PERSISTED,
    [Text] [varchar](max) NOT NULL,
    [Version] [timestamp] NULL,
    [CreatedBy] [int] NULL,
    [CreatedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,

    PRIMARY KEY CLUSTERED ([SynonymId] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

How can I set the CreatedDate to the current date for all of the rows?

Upvotes: 0

Views: 1828

Answers (2)

Rahul Hendawe
Rahul Hendawe

Reputation: 912

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

you can also do this from SSMS GUI.

  1. Put your table in design view (Right click on table in object explorer->Design)
  2. Add a column to the table (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

SSMS GUI

Upvotes: 1

wero
wero

Reputation: 32980

If want to save the date when the record was created, add a default constraint to the table:

ALTER TABLE [Synonym] ADD CONSTRAINT createdDate DEFAULT GETDATE() FOR  [CreatedDate]

Then if you insert a record you don't need to specify the createdDate and get the current date value.

If you want to set the date for all existing records simply run an update

UPDATE [Synonym] SET [CreatedDate] = GETDATE()

or for all records whose CreatedDate is null:

UPDATE [Synonym] SET [CreatedDate] = GETDATE() WHERE [CreatedDate] IS NULL

Upvotes: 2

Related Questions