in Crystal XI, what format is date if default null is selected?

[kratz]

There is a report option to substitute "default" for any null value in the database. "Help" says this is zero. But this doesn't make sense to me for a date field.
1. In a query or formula, what format do I use to reference this default/zero date value?
2. Is there a way to specify a default date value? (For my specific purpose a high value would be far more useful than a low value ... like if I could specify SYSDATE.)

 

[kchaudhry]

In order to check nulls in dates you can use

isnull({table.date})
or
{table.date} = date(0,0,0)

As for sysdate you can use currentdate

Kchaudhry

 

[Madawc]

A date field stores the date in some format you don't get to look at, it does it all for you. Probably it is binary zeros, but that might depend on database. In any case, you use Isnull, as kchaudhry detailed.

You could also say something like

if isnull({your.date}) then "No date"
else ToText({your.date}, "dd/MM/yyyy")

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[BruceBussell]

Ref your note: There is a report option to substitute "default" for any null value in the database. In recent versions of Crystal this convert nulls to default flag appears in File Report options for the existing report, and in File Options Reporting for all new reports.

When these flags are set you can check for converted to default null date fields using the following syntax

If ToText(datefield) = "" Then -- take some kind of action.

The Help is incorrect if it says default null dates = 0

 

[kratz]

Specifically, 'help' says
"Select this check box to have the program convert null values from the database to a 0 (for numeric fields) or a blank (for non-numeric fields)."

In sql I can select where
least(nvl(date1,sysdate),nvl(date2,sysdate),nvl(date3,sysdate)) is in a date range.
But I can't seem to find any straight-forward way to express that in either Crystal or Basic syntax.

If I could change the default for null dates from "zero" to currentdatetime then that would accomplish the same as my 'nvl's. Then I'd still have to compare each of the 3 dates with the others to determine which is earliest, but at least that would make the formula less unwieldy.

(Incidentally, I actually had specified that I'm talking about Crystal eleven.)