Datetime and null value

In my stored procedure i'm extracting datevalues from a table and printing t
hem
in yyyy-mm-dd hh:mi:ss format.
for example if the table value is 'Jul 16 2004 12:00AM' then my statement
(which is dynamically generated)
select Convert(CHAR(20),cast('Jul 16 2004 12:00AM ' as datetime),20)
will print " 2004-07-16 00:00:00 "
but if the table's datevalue is null then the statement
select Convert(CHAR(20),cast(' ' as datetime),20)
is printing "1900-01-01 00:00:00 "
I want the second one to be blank value(' ') what should I do?
Thanks
Chandra
Declare @.t datetime
set @.t='2005-07-16 12:00:00'
select case when @.t is null then convert(varchar,'',101) else
Convert(CHAR(20),cast(@.t as datetime),20) end
set @.t=null
select case when @.t is null then convert(varchar,'',101) else
Convert(CHAR(20),cast(@.t as datetime),20) end
Madhivanan|||Actually I mentioned that it is dynamically generated statement
like the following
select 'insert into employee (hire_date) values ( Convert(CHAR(20),cast('''+
isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
will give you an insert statement.
this insert statement when run, will insert the data into table.
at this point I'm having the problem as the insert statement is inserting
default date(1900...) for empty strings(actually null values)
thanks
chandra
"Madhivanan" wrote:

>
> Declare @.t datetime
> set @.t='2005-07-16 12:00:00'
> select case when @.t is null then convert(varchar,'',101) else
> Convert(CHAR(20),cast(@.t as datetime),20) end
> set @.t=null
> select case when @.t is null then convert(varchar,'',101) else
> Convert(CHAR(20),cast(@.t as datetime),20) end
>
> Madhivanan
>|||Chandra
declare @.dt datetime
set @.dt =''
select @.dt
--1900-01-01 00:00:00.000
select case when @.dt ='' then null else @.dt end as d
--NULL
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:FEA86C39-4E3D-4D66-9588-1421CB53A918@.microsoft.com...
> Actually I mentioned that it is dynamically generated statement
> like the following
> select 'insert into employee (hire_date) values (
> Convert(CHAR(20),cast('''+
> isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
> will give you an insert statement.
> this insert statement when run, will insert the data into table.
> at this point I'm having the problem as the insert statement is inserting
> default date(1900...) for empty strings(actually null values)
> thanks
> chandra
>
> "Madhivanan" wrote:
>|||> Actually I mentioned that it is dynamically generated statement
> like the following
> select 'insert into employee (hire_date) values (
> Convert(CHAR(20),cast('''+
> isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
> will give you an insert statement.
> this insert statement when run, will insert the data into table.
> at this point I'm having the problem as the insert statement is inserting
> default date(1900...) for empty strings(actually null values)
Can you tell us what you WANT to insert when the Hire_date is NULL?
I'll make a guess:
SELECT 'INSERT employee (hire_date)
SELECT '+COALESCE(CONVERT(VARCHAR(8), Hire_date, 112), 'NULL')
FROM employee|||> SELECT 'INSERT employee (hire_date)
> SELECT '+COALESCE(CONVERT(VARCHAR(8), Hire_date, 112), 'NULL')
> FROM employee
Whoops, should be:
SELECT 'INSERT employee (hire_date)
SELECT '+COALESCE(''''+CONVERT(VARCHAR(8), Hire_date, 112)+'''', 'NULL')
FROM employee