Set Default Value for DATETIME COLUMN - don't want to use getDate() want to set to 01/01/1900

Question

Set Default Value for DATETIME COLUMN - don't want to use getDate() want to set to 01/01/1900

Andrew Westgarth

Mr or Mrs. 500

Points: 576
More actions
February 11, 2010 at 6:28 am

#215558

Hi,
I feel like I'm banging my head against a wall of slate at the moment.
I have a table which has a datetime column which up till now has accepted nulls. However We know want to change the column to prevent nulls and insert the date 01/01/1900 as the default value. I don't seem to be able to get SQL to do this though.
I've got the table open in design view, edited the column, removed the allow null and added the default value to '01-JAN-1900' (I've also tried '01/01/1900'; '1900/01/01'; '1900-01-01 00:00:00.000') but then when I select save I get the error:
'users' table
- Unable to modify table.
Cannot insert the value NULL into column 'lastlogin', table 'DataBaseName.dbo.Tmp_users'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I don't seem to be able to get past this. Can anyone help save my sanity?? Whenever I've changed columns which allow nulls to not allow nulls in the past and set the default value SQL has always populated the null columns correctly. Why won't it do the same with datetime columns??
Thanks for any help in advance
Andrew

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

Hi Andrew,

If I understood correctly, the reason you cannot update the column as not null is because you already have nulls in your table. I think you should manually change the values to the default you want, using a SQL script, and then try to do your change.

Here's a little snippet to help:

--=== Update the values that are null to the default value you want

UPDATE yourtable

SET yourdatetimecolumn = '1900-01-01'

WHERE yourdatetimecolumn IS NULL

--=== Then add the constraint you want to have, on the datetime column

ALTER TABLE yourTable ADD CONSTRAINT

DF_test_date DEFAULT ('1900-01-01') FOR yourdatetimeColumn

--=== Then you can go in designer, and specify the column to not allow nulls,

-- it will recreate the table using the new "not null" values, and move your data to it.

Hope that helps,

Cheers,

J-F

Andrew Westgarth Mr or Mrs. 500 Points: 576 More actions · Answer 2

Thanks that did help I had been having issues trying to perform the update but then remembered I had a trigger on the table, I disabled that, performed the update and made the changes then renabled the trigger and have tested and all is working great now.

Thanks 🙂

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 3

Good, happy it helped, and thanks for the feedback, it's much appreciated!

Have a nice day!

Cheers,

J-F