DateTime Formulas

Date/Time Formulas

There are many reporting situations in which you need to manipulate date, time, or date/time data types. Most modern PC and SQL databases support some or all of these data types. Although date and time fields don t appear on the report as pure numbers (they often include other characters and words, depending on how they re formatted), they are actually stored by the database and Crystal Reports as numbers . Therefore, it s possible to do mathematical calculations on date fields. There are also built-in date and time functions that return just parts of date and time fields and that convert other data types to date or time fields.

Number of Days Between Dates

When Crystal Reports performs math on date-only fields, the result of the calculation is in whole days. Crystal Reports returns fractional days if fields in the formula are date/time fields. For example, if you subtract a date-only field containing the value May 1, 2002, from a date-only field containing the value May 5, 2002, the result will be the whole number 4. If the fields are date/time fields and the May 1 field contains a time of 12 noon and the May 5 field contains a time of midnight, the result will be the fractional number 3.5.

So, determining how long it took to ship an order is as simple as creating the following formula:

 {Orders.Ship Date} - {Orders.Order Date} 

Even though both the database fields in this formula are dates (or date/time if these fields are coming from the sample XTREME database included with Crystal Reports), the result of the formula will be a number ”the number of days between the two dates. This formula will return the number of calendar days between the two dates ”it uses all days in the calendar. When you perform this type of date arithmetic, you may find fractional days returned, if the date/time fields contain actual time values. This can cause differences between two dates to be 3.5 or 5.75. This can sometimes be difficult if you really want to know just the number of days between two dates, not including time. In these cases, you can use the Date built-in function to convert the Date/Time data types to just dates. It may be preferable, for instance, to use the following formula to determine the number of days between two dates:

 Date({Orders.Ship Date})  Date({Orders.Order Date}) 

Or, you may also consider using the Round or Truncate function to adjust the numeric result. Round (not a Date- related formula function, but helpful in this example) will round a number to a specified number of decimal places ”rounding up or down, depending on the fractional value. Truncate will simply throw away the fractional portion of the result, not rounding in the process. Accordingly, this would also work to avoid fractional dates resulting from time values in the date/time fields:

 Truncate({Orders.Ship Date} - {Orders.Order Date}, 0) 

If you wish to exclude weekends from this calculation, it gets a little trickier. Crystal Reports provides the Visual Basic “like function DateDiff in both syntaxes to do more flexible date math. An example of using DateDiff to exclude weekends from this type of calculation can be found in Crystal Reports online Help. Search for DateDiff function.

You can calculate a date in the future. If, for example, you want to create an accounts receivable report that shows the actual due date of an invoice, the date itself could be calculated by using the invoice date (a date field) and terms (a number field), as follows :

 {AR.INV_DATE} + {AR.TERMS} 

Although you don t explicitly define this formula as a date formula, it returns a date data type showing the date on which the invoice is due, provided the terms field contains the number of days required for payment (30, 45, and so forth).

Another function in both Crystal Reports formula syntaxes can be a lifesaver with future and past date calculations. The DateAdd function operates much like its Visual Basic counterpart . For example, to determine a date exactly one month prior to today s date, you would use this formula:

 DateAdd("m", -1, CurrentDate) 

The m argument indicates an interval of a month. The second argument indicates the number of time intervals to add (in this case, a negative one, thereby subtracting a month). The third argument is the date or date/time value to add to (Crystal Reports CurrentDate function returns the date from your PC s system clock). What s particularly powerful about DateAdd is the automatic adjustment for various numbers of days in months and years . For example, if you evaluated this formula on March 31, 2000, it would return February 29, 2000 (there is no February 31, but 2000 was a leap year, therefore resulting in February 29).

Tip  

Crystal Reports 10 includes a set of date-related custom functions in the default repository that comes with Crystal Enterprise 10. Some of these custom functions will automatically exclude weekends from date calculations. There s even a custom function you can modify with your own company holidays to exclude these from date calculations, as well as weekends. See Chapter 7 for more information on adding these custom functions to your reports.

Number of Hours and Minutes Between Times

You can also perform mathematical functions directly on time fields. When you calculate two time fields together, the result is in seconds. For example, the following formula will return the elapsed time, in seconds, between a starting time field and ending time field in a college database s course table:

 {COURSE.EndTime} - {COURSE.StartTime} 

If the fields containing the time are actually date/time data types, you won t want to return days between the dates, but seconds between the times. In this situation, you ll need to use the built-in Time function to return just the time portion of a field. For example:

 Time({COURSE.EndTime})  Time({COURSE.StartTime}) 

You may not want the time returned as seconds, but perhaps as hours and minutes, minutes and seconds, or any combination separated with colons. To accomplish this, you have a bit more work to do, but not as much as you might think. Examine the following:

 Time(0,0,0) + ({COURSE.EndTime} - {COURSE.StartTime}) 

You ll notice that parentheses force the time calculation to be performed first, resulting in the number of seconds between the two times. The Time built-in function (used in a different format in this example) is also being used to return a time data type, using three arguments: hour , minute, and second. The particular time being returned by the Time function is midnight. By adding the seconds between the two times to midnight, you essentially have the number of hours, minutes, and seconds that have elapsed since midnight.

When you place this on your report, you ll see hours, minutes, and seconds, followed by AM or PM (AM if the time difference is less than 12 hours, PM if more, assuming that you re using Crystal Reports default hh:mm:ss AM/PM date format). Now it s simply a matter of using the Format Editor to suppress the AM/PM indicator by choosing 24-hour time display. You can also suppress any combination of hours, minutes, and seconds to show the elapsed time the way you wish.

Tip  

The DateDiff function is not limited to just calculating differences between dates. You can also do time calculations similar to the previous example with DateDiff.

Month, Day, Year, Hour, Minute, and Seconds Functions

There are many built-in functions to help you use date and time fields. You can use the Month, Day, and Year functions with a date or date/time field as an argument to return just the month, day, or year of the date as a number. Conversely, with the Hour, Minute, and Seconds functions, you can supply a time or date/time field as a single argument and have just the hour, minute, or second of the field returned as a number.

DateValue Function

Two very important functions are DateValue and CDate, both of which are functionally equivalent. While there are several variations on DateValue, probably the most intriguing is the variation that accepts one string argument. This string can contain several variations of date-like strings, such as 10/1/99, March 17, 2000, 21 Feb 2003, and so on. Crystal Reports will evaluate the string to determine where the month, day, and year portions reside, returning a real date value as the result.

Note  

If you supply a two-digit year to DateValue, Crystal Reports applies a sliding scale approach to determining the century. If the two-digit year is between 0 and 29, Crystal Reports assumes the century is 2000. Otherwise, the two-digit year will be converted to the 1900s.

This greatly simplifies date conversion in Crystal Reports. For example, if your legacy database contains dates in string fields formatted as mm/dd/yyyy, simply use the following:

 DateValue({EMP.HIRE_DATE}) 

There may be times when DateValue can t properly evaluate a date string, due to misspellings or other nonconforming string contents in a database field. This is a prime candidate for a run-time formula error. When you check the formula in the Formula Editor with the Check button, you ll get the popular No Errors Found message. However, when the report runs and the formula encounters the nonconforming string value, you ll see the following:

To avoid these run-time errors, use an If-Then-Else statement (described later in the chapter) in conjunction with another function, IsDate, to perform the conversion to a Date value only if the string can be interpreted by Crystal Reports as a date.

Tip  

A number of related Time and DateTime conversion and detection functions are also available in Crystal Reports. Look in the functions tree or online Help for DateTimeValue, TimeValue, IsDateTime, and IsTime.