Blank or null datetime variable converts to an actual date

27 Jan 2006 2:58 PM

Trishmi

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

27 Jan 2006 3:02 PM

Ryan

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

Show quoteHide quote

"Trishmi" <Tris***@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

27 Jan 2006 3:46 PM

Trishmi

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

Show quoteHide quote

"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" <Tris***@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
>
>
>

27 Jan 2006 7:22 PM

KenJ

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') + ''''