Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:
> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> >
> > Hi,
> >
> > I'm doing dynamic queries. When I try to insert blank or null date data
> > into a table, I'm getting the default value of '01-01-2000' or
> > '01-01'1900'
> > respectively. What I really want it to do is insert a null into the
> > table.
> >
> > I created some sample code below. Can anyone help?
> >
> > create table #mytemptest
> > (testdate smalldatetime null
> > )
> >
> > SET CONCAT_NULL_YIELDS_NULL OFF
> >
> > declare @.query as varchar(1000)
> > declare @.inputdate as smalldatetime
> >
> > select @.inputdate=''
> > select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select @.inputdate=null
> > select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select * from #mytemptest
> > drop table #mytemptest
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''