Brock
asked on
Null dates and 1900-01-01 and sql server 2008
Hello,
We have upgraded Sql Server 2005 to Sql Server 2008.
Date values for a particular variable now display 1900-01-01 when the date is null.
The variable is declared in this way:
Let $Activity_Date = &B.LAST_ACTIVITY_DATE
When I tests for nulls,
let #value_null= ISNULL($Activity_Date)
if #value_null= 1
#DEBUG SHOW '************$Activity_Dat
end-if
In the trace file, I see that the value is null, yet in sql server when this value is inserted into a table it displays as 1900-01-01. I want this to display this as a blank.
I know that a vaule that is 0 or ' ' will display as 1900-01-01 but I clearly can see that the value is null ,
What cast statment can I use in the insert to avoid this.
Thanks,
nigluc
ASKER
Hi David,
In SQR to check for the presence of a value using an if statement, you can use ISNULL. The syntax explanation is this :
ISNULL
-------
Returns a value of one (1) if source_val is null; otherwise, returns a
value of zero (0).
Syntax:
dst_var = isnull(source_value)
source_value = date or text literal, column, variable, or expression
dst_var = decimal, float, or integer variable
Example:
let #null = isnull($date)
************************** ********** ********** ********** ********** ********** ********** ********** ********** *****
What happens next in the program is that we do an insert sql statment:
begin-sql
INSERT INTO PS_NCSF4204
(OPRID
,RUN_CNTL_ID
,EMPLID
,ACCOUNT_TYPE_SF
,TERM_DESCR
,ITEM_NBR
,DESCR
,AMOUNT
,ITEM_TYPE_CD
,LAST_ACTIVITY_DATE
,STRM)
VALUES
($_prcs_oprid
,$_prcs_run_cntl_id
,$Emplid
,$Account_Type_SF
,$_DescrShort
,$Item_Nbr
,$Descr
,#Total
,$Item_Type_CD
, !SNULL ( CONVERT(varchar(10),$Activ ity_Date, 120), ' ' )
,$Term)
end-sql
************************** ********** ********** ********** ********** ********** ********** ********** ********** **
So, I tried the , !SNULL ( CONVERT(varchar(10),$Activ ity_Date, 120), ' ' )
to get a blank date instead of 1900-01-01 but that does not work. When I query NCSF4204, i still see the 1900-01-01 where the dates were supposedly null.
Thank you,
Lucy
In SQR to check for the presence of a value using an if statement, you can use ISNULL. The syntax explanation is this :
ISNULL
-------
Returns a value of one (1) if source_val is null; otherwise, returns a
value of zero (0).
Syntax:
dst_var = isnull(source_value)
source_value = date or text literal, column, variable, or expression
dst_var = decimal, float, or integer variable
Example:
let #null = isnull($date)
**************************
What happens next in the program is that we do an insert sql statment:
begin-sql
INSERT INTO PS_NCSF4204
(OPRID
,RUN_CNTL_ID
,EMPLID
,ACCOUNT_TYPE_SF
,TERM_DESCR
,ITEM_NBR
,DESCR
,AMOUNT
,ITEM_TYPE_CD
,LAST_ACTIVITY_DATE
,STRM)
VALUES
($_prcs_oprid
,$_prcs_run_cntl_id
,$Emplid
,$Account_Type_SF
,$_DescrShort
,$Item_Nbr
,$Descr
,#Total
,$Item_Type_CD
, !SNULL ( CONVERT(varchar(10),$Activ
,$Term)
end-sql
**************************
So, I tried the , !SNULL ( CONVERT(varchar(10),$Activ
to get a blank date instead of 1900-01-01 but that does not work. When I query NCSF4204, i still see the 1900-01-01 where the dates were supposedly null.
Thank you,
Lucy
ISNULL ( CONVERT(varchar(10),$Activ ity_Date, 120), ' ' )
Would definitely produce '1900-01-01' by your own words above as '' or ' ' or 0 will result in default datetime value with is '1900-01-01'. If the value is NULL at some point in your code, something has to be converting this to an empty string or something.
Otherwise, how are you checking if the data is NULL in the database? Are you looking in SQL Management Studio at the raw data ? Just make sure the query to get data isn't using coalesce or isnull to convert to to date 0 if the value is NULL.
Is the column set to allow NULL dates, maybe it has a default of 0 specified.
Would definitely produce '1900-01-01' by your own words above as '' or ' ' or 0 will result in default datetime value with is '1900-01-01'. If the value is NULL at some point in your code, something has to be converting this to an empty string or something.
Otherwise, how are you checking if the data is NULL in the database? Are you looking in SQL Management Studio at the raw data ? Just make sure the query to get data isn't using coalesce or isnull to convert to to date 0 if the value is NULL.
Is the column set to allow NULL dates, maybe it has a default of 0 specified.
ASKER
!SNULL ( CONVERT(varchar(10),$Activ ity_Date, 120), ' ' )
Hi,
Sorry about that it should be a null. Regardless, it did not work. It still gave a null.
I checked the table design. For that particular column , the allow null column is checked off in Sql Analyzer.
Also, as you have suggested, looked in the original table where the value is picked up and
and there there is no coalesce or null being used.
I also did a query on this original table liike this :
Select * from PS_ITEM_SF table where LAST_ACTIVITY_DATE IS NULL.
No rows were returned.
Yet when I feed the value into the statement like this
:Let $Activity_Date = &B.LAST_ACTIVITY_DATE
and insert into the table I get this 1900-01-01 for values that don't exist.
A collegue suggested that I explicitly declare the $Activity_Date as a date variable but that did not work either.
Right now I just suppress it in the Crystal.
Thank you,
Lucy
Hi,
Sorry about that it should be a null. Regardless, it did not work. It still gave a null.
I checked the table design. For that particular column , the allow null column is checked off in Sql Analyzer.
Also, as you have suggested, looked in the original table where the value is picked up and
and there there is no coalesce or null being used.
I also did a query on this original table liike this :
Select * from PS_ITEM_SF table where LAST_ACTIVITY_DATE IS NULL.
No rows were returned.
Yet when I feed the value into the statement like this
:Let $Activity_Date = &B.LAST_ACTIVITY_DATE
and insert into the table I get this 1900-01-01 for values that don't exist.
A collegue suggested that I explicitly declare the $Activity_Date as a date variable but that did not work either.
Right now I just suppress it in the Crystal.
Thank you,
Lucy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you using LINQ?
The corect T-SQL syntax is
if exists(
select 1
from <yourtablename>
where
<yourcolumn> is null
)
<true clause here>
I suspect that the ISNULL($Activity_Date) is responsible for yoru dilema.
Regards
David