Issue with Null Date field

[mcresong]

Hello all,

I am creating a Crystal 8 report from a SQL2K database and having problems displaying a date field when the value is NULL in the db. The formula is included as part of a larger fixed width string. I need it to display Null or blank dates as " " and valid dates to display as yyyyMMdd.

When the formula is :
If IsNull({EInfoAll.birthDate}) or {EInfoAll.birthDate} = date(0,0,0) Then " "
Else
ToText({EinfoAll.birthDate}, 'yyyyMMdd')

The Null Date displays as -4714123

When the formual is :
If IsNull({EInfoAll.birthDate}) or {EInfoAll.birthDate} = date(0,0,0) Then
" "
Else
ToText({EinfoAll.birthDate})
Null Date displays as " "

I need this field formatted as yyyyMMdd. I can't click on the field and use the format option, as this field is included in a larger fixed width string (doesn't appear on the report by itself). I have the Crystal Report options set to convert NULL fields to default values.

Any help would be appreciated.

Thanks !

 

[synapsevampire]

It's probably not a null nor cdate(0,0,0).

Try a simple sanity check, such as:

Right click the date field and select browse data. This will show you what it contains, and I'm guessing null and 0/0/0 aren't in there.

Try the following formula:

If IsNull({EInfoAll.birthDate})
or
{EInfoAll.birthDate} < currentdate - 30000 then
"No Date supplied"
else
ToText({EinfoAll.birthDate}, 'yyyyMMdd')

Adjust the 30000 (number of days to subtract) to allow for the scope of potential birthdays if required.

I'd guess that your database is supplying a value other than null of 0/0/0, such as 1/1/19000. SQL Server does have some funky default value like that.

-k

 

[mcresong]

Thanks for the response. I was able to find a Crystal Reports knowledge base article on this . (#c2006588)

It is just like you said that some databases supply a value other than null.

Thanks for the response !
=======
Synopsis


A Date-time field on a report contains some null values. To check for these null values, this formula is created and inserted on the report:

@CheckNull

{datetimefield} = DateTime(0,0,0,0,0,0)

Although there are definitely null Date-time values in the database, this formula returns false for all the records in the report.


Solution


Some databases read null Date-time values as 'less' than a null value. These 'less than null' Date-time values could display on the report as a blank or as 1/1/-13 or 1/1/-4713.

A better method of checking for nulls in Date-time fields is to use one of following formulas:

@CheckNull1

Date({datetimefield}) = Date(0,0,0)


@CheckNull2

{datetimefield }< DateTime(0,0,0,0,0,0)