Changing default value for a column

I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of
a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.That depends. Are you happy with the existing nulls? If so, simply add a
default constraint:
alter table MyTable
add
constraint DF1_MyTable default (0) for MyColumn
However, if you need to keep all nulls out, then populate the null columns
with some value and then alter the column:
alter table MyTable
alter column
MyColumn int not null
go
Then, add the default constraint as shown above.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:722BC848-15EA-474D-8F40-6BB078F7CDF3@.microsoft.com...
I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of
a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.

Changing default value for a column

I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.
That depends. Are you happy with the existing nulls? If so, simply add a
default constraint:
alter table MyTable
add
constraint DF1_MyTable default (0) for MyColumn
However, if you need to keep all nulls out, then populate the null columns
with some value and then alter the column:
alter table MyTable
alter column
MyColumn int not null
go
Then, add the default constraint as shown above.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:722BC848-15EA-474D-8F40-6BB078F7CDF3@.microsoft.com...
I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of
a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.

Changing default value for a column

I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.That depends. Are you happy with the existing nulls? If so, simply add a
default constraint:
alter table MyTable
add
constraint DF1_MyTable default (0) for MyColumn
However, if you need to keep all nulls out, then populate the null columns
with some value and then alter the column:
alter table MyTable
alter column
MyColumn int not null
go
Then, add the default constraint as shown above.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:722BC848-15EA-474D-8F40-6BB078F7CDF3@.microsoft.com...
I have an existing database and I want to change it so that if no value is
inserted into a column that it populates that column with a zero instead of
a
null value. How do I make this change? I suspect I need to change all the
existing nulls to zero before making the change? Any help would be greatly
appreciated.

Changing date to string of numbers

I basic question but can someone help.

I have a SELECT statement, the result of which populates adatagrid. The first column has consecutive dates in it and I wantto hyperlink each date to a seperate Javascript function (theJavascript is created on the fly and is unique for each date). Ineed a different function name for each function and so tried the datebut "/" is not allowed in the Javasript function name. I thinkthe easiest way will be to produce a new column with the date expressesddmmyyyy, ddmmyy or some such unique number (but not dd/mm/yyyy). I tried :-

"CASE " & _
"WHEN t3.date = t3.date THEN (DAY(t3.Date) + MONTH(t3.Date) + YEAR(t3.Date)) ELSE NULL END AS [javaKey]

but this adds the year to the month to the day - not a unique result as 1/2/06 and 2/1/06 are the same.

I am just getting to grips with VB.Net (as an amature) but am a distinct beginner at SQL!

Many thanks

Mike

Hi Mike,

You can use the ISO format in this place:

CONVERT(NCHAR(8), [Date], 112)AS newDate

the date format will be yyyymmdd.

You can always check CONVERT DATE function from Books Online to convert your date.

Hope this helps.

|||Hi Limno

Many thanks. Your reply is just what I need. It works great.

I am sorry that could not figure it out for myself. I do usebooks on line and I have "Microsoft SQL Server 2005 A Beginner'sGuide" (which I got before I realise my server uses 2000!) and "SAMSTeach Yourself SQL 24 Hours". I started off learning VB.net butas my project goes on, rather than feeling that I am becoming competentat producing the web pages I want, I seem require more and moreknowledge (like SQL, JavaScript & CSS). Sometimes I feel I amgetting there, the next minute feel totally inadequate!! I strive tolearn, and in the meantime I really do appreciate the help of peoplelike yourself.

Many many thanks for your time and patience.

Regards

Mike