sql query for alter column

Question

3.00/5 (1 vote)

See more:

hi i added a column in my table. but i added it as null. i want to change it as not null.when i change using this query

alter table tbl_user_login alter column created_date datetime not null

it shows an error
Cannot insert the value NULL into column 'created_date', table 'customer_habitat_afsal.dbo.tbl_user_login'; column does not allow nulls. UPDATE fails.

what can i do?
help me..
thanks in advance

Posted 25-Nov-11 22:40pm

afsal.mp

Updated 25-Nov-11 22:44pm

Eduard Lu

v2

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Answer 1 · 2011-11-26T01:18:00

Solution 3

Firstly fill the null values with an arbitrary data for example current date time then alter that column :

SQL

update tbl_user_login set created_date= getdate() where created_dateis null ;


alter table tbl_user_login alter column created_date datetime not null

Hope it helps.

Posted 26-Nov-11 1:18am

Amir Mahfoozi

Comments

RaviRanjanKr 27-Nov-11 14:34pm

My 5+

Mehdi Gholam · Answer 2 · 2011-11-25T23:28:00

Solution 2

SQL Server is checking the values in the table so they do not violate the not null and they do hence the error.

You can insert values into the null columns and rerun the query.

Posted 25-Nov-11 23:28pm

Mehdi Gholam

Prosan · Answer 3 · 2012-06-15T19:16:00

first enter current date or anyother date in this field as you want than alter the column. then it will work. because in your field there is null value stored so it is giving error.

SQL

update tbl_user_login set created_date=@Date where created_date is null
alter table tbl_user_login alter column created_date datetime not null

Eduard Lu · Answer 4 · 2011-11-25T22:43:00

Solution 1

Do you really need to programmaticaly set this one? Just edit the field in design view.

Regards,
Eduard

Posted 25-Nov-11 22:43pm

Eduard Lu

Milind Karpe · Answer 5 · 2012-06-15T02:52:00

Solution 4

here is tutorial to alter a table...

http://www.gigasters.com/post.aspx?postid=18

Posted 15-Jun-12 2:52am

Milind Karpe