Crystal Reports – changing date format from 20150131 to 31/01/2015

A client had dates coming into her report in the format 20150131 when she wanted it in the format 31/01/2015.

Crytsal Reports dates

The formula I used to convert this made use of the LEFT, MID and RIGHT functions:

RIGHT({Orders_.Date},2)&”/”&MID({Orders_.Date},5,2)&”/”&LEFT({Orders_.Date},4)

In this instance I am using field Date from table Orders_. The RIGHT function looks at the field and takes the rightmost so many characters, so in this case it is taking the last two characters of the date.

The MID function looks for the xth character along and then takes a number of characters starting from there, so in this case it is taking the 5th and 6th characters.

The LEFT function looks for the left most characters in the string and takes the number of characters asked for in this case the first four characters in the string.

To join all the bits together with the / character we need to use joining factors – you can use either & or + as the joining factor and because the / is a string it needs to be enclosed in double quotes.

The result is as shown above. However this is still a string and if I want to find the difference between two dates, for example, I will need to convert it to a date.

Going back to my formula, I add CDATE( at the beginning of the formula and and extra ) at the end of the formula.

CDATE(RIGHT({Orders_.Date},2)&”/”&MID({Orders_.Date},5,2)&”/”&LEFT({Orders_.Date},4))

The string now becomes a date and I can use it as a date for other formulas, parameter queries, etc.

Find out more about our Crystal Reports training Sydney, Australia or our Crystal Reports courses London, Surrey, Middlesex or our Crystal Reports training Dorset, Hampshire, Wiltshire.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Crystal Reports and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s