"Bad Date Format String"

[technonurse]

I am experiencing what seems to be a random problem. All of the dates I pull into CR are string fields which I convert to date fields. Most of the time these fields work fine, but sometimes when I rerun a report, which has been successful initially, I will get this error "Bad date format string." I have examined the SQL expression and cannot find any differences between the fields which consistently work and those which all of sudden malfunction.

I am using CR 8.5. My links are all common joins because the tables being used are all one-to-one relationships. Technonurse-Spokane

 

[clareesh]

Is what you passed in in this format?
Date (yr, mth, day)

 

[Naith]

Assuming you're using date conversion, or forcing a format, how are you doing this?

Also, does your database field allow for null values? If so, you will need to start your conversion and display with a null check.

Naith

 

[technonurse]

My date field that comes in is in this format: yyyy, mm, dd.

I write my formula as {my_field}[5 to 6] +"/"+
{my_field}[7 to 8] +"/"+
{my_field}[1 to 4

I do not write anything in to allow for nulls. Not sure how. Technonurse-Spokane

 

[Naith]

I expect that you have some null values in your database, and the report is choking when it's looking for positions [5 to 6] etc when there are no positions to be had.

Try this:

If Not IsNull({my_field})
Then
{my_field}[5 to 6] +"/"+
{my_field}[7 to 8] +"/"+
{my_field}[1 to 4]
Else '';

Naith

 

[synapsevampire]

You might also qualify it further to allow for bad dates:

If Not IsNull({my_field})
and
len({my_field}) = 8
Then
{my_field}[5 to 6] +"/"+
{my_field}[7 to 8] +"/"+
{my_field}[1 to 4]
Else '';

You can also eliminate nulls within the report by selecting:

File->Report Options->Convert null values to default

-k

 

[technonurse]

Suggestions all worked great. I believe this fixed my problem. Thank you! Technonurse-Spokane