Wednesday, April 25, 2012

Crystal Reports : Dealing with Dates as Plaintext

It is often that you might see date values being stored in plaintext. That is, in a String data type (opposed to Date or DateTime). Now, let's imagine we are trying to perform some date math on a field. Something simple, like calculating the number of days that have passed.

Let us assume our data field {CONTACT1.KEY4} is actually a String field, and we see a mix and match of formats, like so;

01/01/2012

January 1st, 12

Jan 1 12

Jan 1st 2012

...so on and so forth. Our first task is to convert the actual incoming field data into a Date Type so we can easily perform our math. Because all we really want to do is create a formula where we can use the following expression; CURRENTDATE - {CONTACT1.KEY4}.

To convert our {CONTACT1.KEY4} to a Date Type, create a new formula and use the following code:

DATE({CONTACT1.KEY4})

..which just tried to convert our plaintext date to an actual Date value. But if you're working with a mix and match of date formats, you may get the following error:

Bad Date Format String

...and this will prevent the report from running at all. What is actually happening is that values like '01/01/2012' and 'Jan 1 2012' are "valid", while values like 'Jan 1 12' are not. So, we now need to do some testing within our formula, like so;

IF ISDATE({CONTACT1.KEY4}) = TRUE THEN DATE({CONTACT1.KEY4})

When we use the ISDATE() function, we are testing to see if Crystal thinks the input is a "valid date". And only if this is TRUE do we continue on and actually convert our date value. So now we should be able to run our report without interruption. For the "bad" date values, our formula will return blanks.

ProTip: If you need to clean up the underlying database before your report can be useful, you could always add ISDATE({CONTACT1.KEY4}) = FALSE to your Select Expert to run a list of all "bad" date values.

1 comment:

maymay said...

On 2007, the great thing with the louis vuitton replica sale universe is without a doubt which will Chanel announced the revolutionary restrained type and even exceptionally sought after purse. Through primarily 13 for sale around the globe, it purse is found for that supplement throne among the most high-ticket expensive wholesale handbags globally. That fake chanel Engagement A long time Old classic Backpack debut on January dazzling through 3. 56 carats characterized by 334 precious stones, 16 carat the white kind of golden computer, and even the white kind of alligator body. Six worth mentioning over the top wholesale handbags can be for sale in the us. The cost in this particular wide variety is known as a spectacular $261, 000. Absolutely yes that louis vuitton replica belts is without a doubt night increased, though a lot of these plastic bags can be works of art, art pieces. Through primarily 13 to be crafted, maintaining you can end up very much like a craft enthusiast running a Picasso. Aquiring a Teacher purse ıs going to be the most impressive variations on designer handbags and even wholesale handbags that folks will be able to opt to contain. In cases where everybody requires for you to include superb purse which can secure anybody efficient through gucci replica bags, some people will likely need to find a Teacher purse in the hermes replica hat on their possess mixture. It might now and again be particularly quite easy rather than each one of these may likely in pondered.