Date Formula Error

[aspnetuser]

I am using the following formula to convert a sql server 2000 database field to a date.
Numbervar TheYear:= ToNumber({check_date}[1 to 4]);
Numbervar TheMonth:= ToNumber({check_date}[5 to 6]);
Numbervar TheDay:= ToNumber({check_date}[7 to 8]);

Date(TheYear,TheMonth,TheDay)

The error I get is show below.
The string is non-numeric.

I looked in the database and their are some null fields if that will help solve this?

Please, any suggestions???

 

[synapsevampire]

Please post the format of what's in the field and it's current data type in SQL Server. Right click it in Crystal and select Browse Data.

Crystal is telling you that you have a bad date format in that field.

And there are many ways to convert it, SQl Expression being the most efficient.

Try using a check for it being numeric, or learn the fields format.

Example:

Numbervar TheYear;
if isnumeric(ToNumber({check_date}[1 to 4])) then
TheYear:=ToNumber({check_date}[1 to 4])
else
TheYear:=0

etc...

-k

 

[aspnetuser]

the data type is string with a length of 8

i am using CR 8.5 professional

not sure how to fix this?

if i leave out the null values, my current formula works.

if i don't, it gives the error message i posted above

 

[synapsevampire]

If there are nulls, try:

if not(isnull({table.field})) then
cdate(val({check_date}[1 to 4]),val({check_date}[5 to 6]),val({check_date}[7 to 8]))
else
cdate(1900,1,1)

-k

 

[aspnetuser]

that worked!

I get the cdate part but i don't understand the else ?

I don't display a 1/1/1900 for the null dates on my report. That is just what I wanted so it is perfect but can you explain why the 1/1/1900 does not appear?

 

[kskid]

The else is the default date for the null value.

If you don't use the null value records in your report, you could eliminate them altogether when you perform your record selection

not (isnull({table.field}) or {table.field = "")

 

[kskid]

Sorry. misread your post.

 

[aspnetuser]

i still do not understand the solution? It works but I would like to know how?

 

[synapsevampire]

The 1/1/1900 should be showing up unless you have something which is intentionally omitting them, but that doesn't make sense if you're getting an error without the formula.

You still never posted examples of what's in the field, right click the field (not this formula) and select browse data.

If you have either the Report->Report Options->Convert null value to default or you are filtering in the record selection formula, such as:

not(isnull({table.date}))

Then they wouldn't show up.

You say that you don't displa 1/1/1900, do you mean the report isn't or that you are explicitly not doing so using code?

-k

 

[bdreed35]

If you use the 1/1/1900 as the default, you would need to suppress it conditionally if you didn;t want to view it on the report.

I use what Crystal considers a "null" date value, since Crystal won't display it:

if not(isnull({table.field})) then
cdate(val({check_date}[1 to 4]),val({check_date}[5 to 6]),val({check_date}[7 to 8]))
else
cdate(0,0,0)

~Brian