troubleshooting Question

datetime create table syntax differs from alter table?

Avatar of tbeernot
tbeernot asked on
Microsoft SQL Server
8 Comments1 Solution1461 ViewsLast Modified:
Ok, I'm going slightly mad.

This is working ok on SQLServer 2000 SP4:

create table test
(
      lastmodified datetime not null default getdate()
)

This is not working:

alter table test alter column lastmodified datetime not null default getdate();

"Incorrect syntax near the keyword 'default'."

Que?

I need to add a lastmodified field to a table where new records get the current date & time as its default value.

alter table x add lastmodified datetime;
update x set lastmodified = '01/01/2000 12:00:00 AM';
alter table x alter column lastmodified datetime not null default getdate();
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros