Bad Date Format

[Folkstone]

Creating a formula to show days between two different fields. One of the fields is a date field and the other is a string field. I have tried to convert the string field into a date field by using the "CDate" function and I am finding that there are a few dates which are coming up as "Bad Date Format". For example, the 16/06/2004 is not being registered as a date. I have browsed the data in this field and I can see this as a date in the data. I have tried everything to get this formula to work however I cannot seem to rid myself of this error message. This error message is causing the report to stop running until it is fixed.

Any ideas?

 

[crystalvictim]

how exactly does your formula (using CDate) look like ? CDate is supposed to be able to deal with numbers, dates and strings (at least in CR8.5).

 

[bdreed35]

You may need to reformat you string, or break it up like this:

(Assuming string format of 16/06/2004)

stringvar strDate := "16/06/2004"; //replace with your field
stringvar array arrDate := split(strDate,"/");
cdate(val(arrDate[3]),val(arrDate[2]),val(arrDate[1]))

~Brian

 

[Folkstone]

Tried the above formula and I am now receiving the following error message from bdreed35:

A subscript must be between 1 and the size of the array

Any ideas? Only new to crystal and I am more using trial and error then knowledge at the moment.

 

[synapsevampire]

Quit sending that error, Brian, that's not nice

You have inconsistent data, so you need to check for the different conditions.

One way would be to check the data, as in:

stringvar strDate := "16/06/2004"; //replace with your field
stringvar array arrDate := split(strDate,"/");
if ubound(arrDate) = 3 then
cdate(val(arrDate[3]),val(arrDate[2]),val(arrDate[1]))
else
cdate(0,0,0)

Now the error should go away.

But you'll likely get unexpected results, so use a LEN to determine if the data is in the correct format, so create a sanity checking report with something like tyhe following in the Report->Edit Selection Formula->Record:

len({table.stringdate}) <> 10

Also technical information would help:

Crytsal version
Database/connectivity used
Example data
Expected output

-k

 

[Folkstone]

Tried to above and I am now getting an error message as follows:

Dates must be between year 1 and year 9999

I have browsed the data in the string field and I have found that some of the dates are shown as 16/06/2004 and others are shown as 16/06/04. I think that this is why I am receiving an error message saying Bad date format. Anyway that I can indicate that that field should be read as dd/mm/yyyy?

Thanks for all your help

 

[bdreed35]

I will try to quit sending that error. I will play nicer this time

You can try it this way. It is a little more tolerant of 2 digit years.
[/code]
stringvar strDate := "16/06/04"; //replace with your field
stringvar array arrDate := split(strDate,"/");
if ubound(arrDate) = 3 then
cdate(totext(arrDate[2]&"/"&arrDate[1]&"/"&arrDate[3]))
else
cdate(0,0,0)

~Brian

 

[bdreed35]

That should have been:

stringvar strDate := "16/06/04"; //replace with your field
stringvar array arrDate := split(strDate,"/");
if ubound(arrDate) = 3 then
cdate(totext(arrDate[2]&"/"&arrDate[1]&"/"&arrDate[3]))
else
cdate(0,0,0)

~Brian