DateSerial( ) and TimeSerial( )

The DateSerial() and TimeSerial() functions can be used to create a date or time by passing the parts of the value as separate arguments to the function. The DateSerial() arguments are the year, month and day. The DateTime() arguments are the hour, minute, and seconds.

In the simplest form, these functions create a date or time using three arguments. But these functions are also very powerful for adding and subtracting values to a Date and Time. They are different from the DateAdd() function in that they perform the calculations using a cumulative process. They start by calculating a partial date (or time) value and then build upon it and modify it each step of the way. It starts by calculating the year, then it calculates the month and finally the day. This is easiest to understand by looking at a simple example first and then a more complex example. All of the examples use the following statements to declare and initialize the variable MyDate.

This code snippet shows the variable declaration that is used for the remaining examples.

//Declare the variable for use in the examples
DateVar MyDate;
MyDate := CDate(“2/4/2006”);

This next code snippet gets the current year and month from the current date and passes them to the DateSerial() function. It passes the value 1 as the day argument to force it to return the first day of the current month.

DateSerial(Year(MyDate), Month(MyDate), 1); ‘Returns 2/1/2006

The next function calculates the last day of the prior month by using each argument to create the next part of the date in sequence and then modifying the result according to the arithmetic.

Formula = DateSerial(Year(MyDate), Month(MyDate), 1 – 1) ‘Returns 1/31/2006

How it calculates the result is best shown using the steps listed here.

1. Calculate the year. This returns a date with the year of 2006
2. Calculate the month. This returns a date of 02/2006.
3. Calculate the day. The first part of the argument is 1 and this returns a date of 02/01/2006.
4. The subtract operator tells it to subtract one day from the date as it has been calculated to this point. Thus, it subtracts one day from 02/01/2006 to give a date of 1/31/2006.

The next example is the most complex, but uses the same rules as the last example. It calculates the last day of the current month.

DateSerial(Year(MyDate), Month(MyDate) + 1, 1 – 1); //Returns 2/28/2006

1. Calculate the year. This returns a date with the year of 2006.
2. Calculate the month. This returns a date of 02/2006
3. The addition operator tells it to add one month. This returns a date of 03/2006.
4. Calculate the day. The first part of the argument is 1 and this returns a date of 03/01/2006.
5. The subtract operator tells it to subtract one day from the date as it has been calculated to this point. Thus, it subtracts one day from 03/01/2006 to give a date of 2/28/2006.

You can see from the three previous examples that using a cumulative approach to calculating the date is very powerful. It’s almost like using a single function to write a simplified macro.

Timer

The Timer function returns the number of seconds that have elapsed since midnight. This can be used for doing performance evaluations of how long it takes a report to run. Unfortunately, it is only significant to the nearest second. So it is only useful for analyzing reports that have lengthy run times. The following code demonstrates timing how long it takes a report to run.

In the Report Header put the following formula:

BeforeReadingRecords;
Global NumberVar StartTime;
StartTime := Timer;
“”; //Need to at least return an empty string even though it isn’t used

In the report Footer put the following formula:

WhilePrintingRecords;
Global NumberVar StartTime;
Timer – StartTime;
//This returns the number of seconds it took to run the report

Other Functions

Although this chapter and the last two have listed many useful functions, Crystal Reports still has many more to choose from. You’ve seen all the primary ones and I’m going to leave the remaining ones for you to explore on your own.