Is it possible to change the default value of - 1900-01-01 00:00:00.000 for PSDATE column at DB level in SQL Server 2005? If yes please let me know how we can change this.

Filed under: Programming, Transact-SQL (T-SQL) — Ari Weil at 3:23 am on Thursday, February 7, 2008

There was a thread I saw on dotnet247 that discussed this behavior, but I’m not sure if it’s what you’re looking for since the goal was to insert a NULL value. What do you want to see instead of 1/1/1900 00:00:00.000? You can always talk to your DBA and ask him to create a default on the relevant column so that a different default value is used.

You could use a default of GETDATE() - which could lead to really bizarre behavior if this is a birthdate…
ALTER TABLE [yourtablename]
ADD CONSTRAINT DF_[yourtablename]_[yourcolumnname]
DEFAULT GETDATE() FOR [yourcolumnname]

Or you could use a different date of your choosing
ALTER TABLE [yourtablename]
ADD CONSTRAINT DF_[yourtablename]_[yourcolumnname]
DEFAULT ‘1978-10-20 04:16:00.000′ FOR [yourcolumnname]

When you insert the table default, you’ll have a value other than 1/1/1900 00:00:00.000.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>