Default Field Value for DateTime & SmallDateTime

In SQL Server 2000 / Asp.Net I am trying to use default values for all fields; hoping to eliminate nulls.

For number and character fields, the default is pretty obvious, but is there any empty value for a date field? I think a null there might be better than putting in a bogus date, at least it can be tested for.

Are there any more developend ideas on this question?

Many thanks
Mike ThomasHi, Mike.
The choise depends directly on the problem U r solving. Sometimes GETDATE() helps... just analize Ur task and make a corresponding conclusion: what value is permitable as a default one in the definite case...

Alex.sql

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
-Scott ElgramScott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> this
>

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
-Scott Elgram
Scott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>
|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> this
>

Default Field Value

Is there a way to set default values for a numeric field? I have several
fields that sometimes are null. I want the nulls to show as zero. I tried
iif(value is null, 0.00 , value) but it gives an error on the â'nullâ' word. I
tried â'IsNullâ', â'IsNothingâ' and â'IsNumericâ' all with the same error.
Any ideas?Try
iif(value=Nothing, 0.00 , value)
"tachtenberg" <tachtenberg@.discussions.microsoft.com> escribió en el mensaje
news:9FF1A567-6AB0-402F-A542-8205EB6AA195@.microsoft.com...
> Is there a way to set default values for a numeric field? I have several
> fields that sometimes are null. I want the nulls to show as zero. I
> tried
> iif(value is null, 0.00 , value) but it gives an error on the "null" word.
> I
> tried "IsNull", "IsNothing" and "IsNumeric" all with the same error.
> Any ideas?
>

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
--
-Scott ElgramScott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > I have a table with a filed set up to use a default Field value of
> > (GetDate()) but is also allowed nulls. However, The default value for
> this
> > field has stopped being set automatically. Has anyone experienced this
> > problem before? Does anyone know why and/or how to fix it?
> >
> > --
> > -Scott Elgram
> >
> >
>