Member Avatar for dieterm

Hi,

How do I design a table column named UpdateDate that will give the default date and time upon inserting a record into the table

I'm using Enterprise Manager in MSSQL

Many Thanks

  • 6 Contributors
  • 7 Replies
  • 403 Views
  • 6 Years Discussion Span
  • Latest Post Latest Post by Member #947608

Dani AI

Generated

Short answer: add a DEFAULT constraint on the UpdateDate column (and choose the right data type). ’s original question is exactly that use case; suggested now() (which caused the error saw because that function isn’t for SQL Server). Later replies pointed toward the correct server-side approach ( and and ).

A practical, modern pattern:

  • Use a precise data type (datetime2 or datetimeoffset) so you don’t lose fractional seconds or timezone info.
  • Give the column a DEFAULT that emits the current timestamp so inserts that omit the column get a value automatically.

Example (create with a default timestamp and millisecond precision):

CREATE TABLE dbo.YourTable (
  Id INT PRIMARY KEY,
  UpdateDate datetime2(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

If you need UpdateDate to change automatically on UPDATE (i.e., “last modified”), add a small AFTER UPDATE trigger rather than relying on the insert default:

CREATE TRIGGER dbo.trg_YourTable_UpdateDate
ON dbo.YourTable
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  UPDATE t
  SET UpdateDate = SYSUTCDATETIME()
  FROM dbo.YourTable t
  JOIN inserted i ON t.Id = i.Id;
END;

Troubleshooting and tips: defaults only apply when the column is omitted (or DEFAULT is explicitly used); inserting NULL will store NULL. If you want UTC, use SYSUTCDATETIME or datetimeoffset and convert in the application. For auditing, keep CreatedDate (default on insert) and ModifiedDate (trigger on update) as separate columns. If you’re using Enterprise Manager’s UI, enter the default expression into the column’s “Default Value or Binding” field or add it with ALTER TABLE when modifying an existing table.

Recommended Answers

All 7 Replies

Member Avatar for weilander

In the column definition: default value = now()

Member Avatar for dieterm

Thanks will try it

Member Avatar for WileECoyote

Hi,

How do I design a table column named UpdateDate that will give the default date and time upon inserting a record into the table

I'm using Enterprise Manager in MSSQL

Many Thanks

It doesn't work for me. I have an error message when trying to use now().

Member Avatar for campkev

create table tblname (
fieldname datetime default getdate()
)

Member Avatar for wieland

In the column definition: default value = now()

Err: user must input default value to = ( getdate() )

With this SQL will insert the date + time everytime someone inserts values in that columns.

Member Avatar for Member #947608

Hi,
I have created two columns in the DB and I want to insert fill these two columns with current datetime.How Can I do It,Please reply

CheckInTime datetime,
CheckOutTime datetime
insert CheckTime values(1,'','',and what should I do for the datetime)
Thank you.
Regards.

Member Avatar for Member #947608

I just Passes

GetDate()

and its works Thank You

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.