Need help reformatting strings to dates

[antihippy]

Hi guys,

I'm relatively new to Crystal Reports and have spent the last wee while searching through the web for help converting strings to dates. I'm using CR 8.5.

The dates look like this: "2003-07-11 09:22:00" [they are of the string type]. Other entries for the the column are "N/A".

Basically I imagine an If-Then-Else of the type:

If column looks like this "2003-07-11 09:22:00"
then convert it to DateTime type,
Else leave it as "N/A"

I can't get the If-Then-Else loop to work. Additionally where should I place the formula? The data for the report is pulled from an MS SQL Server stored procedure.

I would appreciate any help on this you can give. If this is a common problem can you point me to previous posts that are appropraite [I couldn't find anything when I searched].

 

[Naith]

Antihippy,

Your formula approach as it stands will generate an error because the 'then' and 'else' clause essentially return mixed datatypes. Both 'then' and 'else' should return either datetime results together or string results together.

As far as converting is concerned, can you not simply use:

DateTime({YourStringHere})

Is there likely to be non-date related information in this field, or are you trying to get the N/A to appear for null values?

Naith

 

[synapsevampire]

A formula cannot display different data types, such as either display a datetime type, or display a string, it's specifically typed.

To resolvet, create a formula which converts the field to a datetime:

//MyDatetime formula
cdatetime({table.datetimefield})

Now if you want to display "N/A" if the date isn't valid, you can use a formula such as:

If isnull(@MyDatetime) then
"N/A"
esle
totext(@MyDatetime)

Note that the isnull() has to be first in the formula.

Also note that this field may NOT contain null, rather a default value of some sort, so check this first.

This assumes that you want a real date type field, if you don't really need a date type. just use:

if isnull({table.datetimefield})
or
{table.datetimefield} = ""
then
"N/A"
else
{table.datetimefield}

-k

 

[antihippy]

The field contains strings which contain either the date in the format I've listed or "N/A". I want to seperate out the dates and use them as dates but leave the "N/A"in place. Either that or convert the "N/A"to a null value. The developer who wrote the code originally wants to leave the output as a string rather than return a date value and nulls [and therefore make my life easier].

As you have said the I get an error because of the mixed datetpyes. It's a thorny issue.

 

[antihippy]

I apologise, I had approached this from the wrong angle. What I have done is to remove the problem trainling characters (in this case the part corresponding to the time) and I have got the output I was looking for.

Thanks for your help.