Null value in datetime causing problem

Null value in datetime causing problem

Post by Anna » Wed, 03 Apr 2002 23:56:44



Hi,
  I am trying to use DTS for transferring data from sql
server to a text file.I have 15 columns in the table and
my last column is of data type datetime and it has a null
value in it.So my text file has a space in that column.As
this is the last column when I try to move my text file
back to a new table it shows me only 14 columns as the
last column is empty.In my vb transformation script I
tried to replace an empty string with a null when I move
back to the table.But my Vb code is not trapping
isnull,=Null or =''.Can somebody help me!!

Thanks
Anna

 
 
 

Null value in datetime causing problem

Post by Allan Mitchel » Thu, 04 Apr 2002 03:30:07


In the source Query could you not do

ISNULL(DateTimeFiekd,'')

--

Allan Mitchell
www.allisonmitchell.com


Quote:> Hi,
>   I am trying to use DTS for transferring data from sql
> server to a text file.I have 15 columns in the table and
> my last column is of data type datetime and it has a null
> value in it.So my text file has a space in that column.As
> this is the last column when I try to move my text file
> back to a new table it shows me only 14 columns as the
> last column is empty.In my vb transformation script I
> tried to replace an empty string with a null when I move
> back to the table.But my Vb code is not trapping
> isnull,=Null or =''.Can somebody help me!!

> Thanks
> Anna


 
 
 

Null value in datetime causing problem

Post by Anna » Thu, 04 Apr 2002 05:16:40


Nope Isnull function is not working!!
Anna
>-----Original Message-----
>In the source Query could you not do

>ISNULL(DateTimeFiekd,'')

>--

>Allan Mitchell
>www.allisonmitchell.com



>> Hi,
>>   I am trying to use DTS for transferring data from sql
>> server to a text file.I have 15 columns in the table and
>> my last column is of data type datetime and it has a
null
>> value in it.So my text file has a space in that
column.As
>> this is the last column when I try to move my text file
>> back to a new table it shows me only 14 columns as the
>> last column is empty.In my vb transformation script I
>> tried to replace an empty string with a null when I move
>> back to the table.But my Vb code is not trapping
>> isnull,=Null or =''.Can somebody help me!!

>> Thanks
>> Anna

>.

 
 
 

Null value in datetime causing problem

Post by Darren Gree » Thu, 04 Apr 2002 05:51:15




Quote:>Nope Isnull function is not working!!
>Anna
>>-----Original Message-----
>>In the source Query could you not do

>>ISNULL(DateTimeFiekd,'')

Allan means the Source SQL statement, so you are using the T-SQL ISNULL
function as oppose to VBScript. This means you won't get any nulls when
working in the transformation, just empty strings.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

 
 
 

Null value in datetime causing problem

Post by Allan Mitchel » Fri, 05 Apr 2002 04:38:51


Darren

You put it so much better than I

--

Allan Mitchell
www.allisonmitchell.com





> >Nope Isnull function is not working!!
> >Anna
> >>-----Original Message-----
> >>In the source Query could you not do

> >>ISNULL(DateTimeFiekd,'')

> Allan means the Source SQL statement, so you are using the T-SQL ISNULL
> function as oppose to VBScript. This means you won't get any nulls when
> working in the transformation, just empty strings.

> --
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com

 
 
 

Null value in datetime causing problem

Post by Darren Gree » Fri, 05 Apr 2002 07:53:54




Quote:>Darren

>You put it so much better than I

The benefit of hindsight :)
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
 
 
 

1. NULL Date Value cause problem in Dimension?

I found that I am missing some data measures from current records, it turns
out these records have <NULL> value on one of the columns. This column is
one of the dimension of my cube (SQLServer7). When the cube try to find
distinct values from this column to build the dimension, it can a [0] year
[] month [] day member, I think this is the way it interpret my <NULL>
records, unforturately it don't know how to relation this back when it build
the aggregations.

Any work around for this? What value is Time.[0].[].[]? My only thought is
set "01/01/1900" to all <NULL>.

Thank in advance

2. To all you ADO experts out there

3. Export Null values to Excel cause left shifted values in XLS

4. A Lamp for the dark please

5. Null values: Classic null value problem (?)

6. pgsql-server/doc/src/sgml plpython.sgml

7. Null value in date field causes error 524

8. ado 2.7 killing apps (Oracle adBstr)

9. Null value of DATETIME is CONVERT to 1/1/1900

10. datetime w/null time values

11. ORACLE DISTRIBUTED QUERIES, DATETIME FIELDS , NULL VALUES