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

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
> >
> >
>

Default date in a derived column expression

one of my SSIS packages use this expression to put todays date in if it is NULL:

(ISNULL(datejs)) ? GETDATE() : datejs

however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.

Is it possible to do what I want it to?

Thanks

Try casting it

(ISNULL(datejs)) ? (DT_DBTIMESTAMP)"2007-01-01" : datejs

|||Fresh from the Integration Services Expression Reference section of Books Online, we have the GETDATE (http://msdn2.microsoft.com/en-US/library/ms139875.aspx) topic. So using GETDATE is possible, but sounds like a variable or literal casted as you suggest is actually what is needed here.|||

Larry Charlton wrote:

Try casting it

(ISNULL(datejs)) ? (DT_DBTIMESTAMP)"2007-01-01" : datejs

That's done it. Thank You.