Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Null dates and 1900-01-01 and sql server 2008

Posted on 2010-08-28
5
Medium Priority
?
1,413 Views
Last Modified: 2012-05-10

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_Date here'  $Activity_Date
    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
0
Comment
Question by:Lucia
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 33552295
Hi,

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
0
 

Author Comment

by:Lucia
ID: 33553046
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),$Activity_Date, 120), ' ' )
                      ,$Term)
end-sql
 

   ************************************************************************************************************
So, I tried the ,  !SNULL ( CONVERT(varchar(10),$Activity_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
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 33553244
ISNULL ( CONVERT(varchar(10),$Activity_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.
0
 

Author Comment

by:Lucia
ID: 33553853
 !SNULL ( CONVERT(varchar(10),$Activity_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





 
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 33553981
Hi Lucy,

I've found that things work better when I return the datetime to crystal as a datetime - including nulls - and let Crystal worry about the formatting.

If on the other hand you convert the datetime to a varchar in SQL, then that can affect sorting. The datetimes will in a properly built anything sort, whereas once they are string values all bets are off.

08-30-2010
08-31-2010
08-4-2010
08-5-2010

... for instance

HTH
  David
 
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question