Working with Date and Time @Functions

Compared to the amount of work that you have to go through in LotusScript to work with time-date values, it is a pleasure to work with them in the Formula language. Table 12.6 lists a number of @Functions that you will find useful with time-date values.

Table 12.6. Time-Date @Functions

@Function	Usage
@Accessed	The last date a document was accessed
@Adjust()	Modifies a date, adjusting it into the past or the future
@BusinessDays	Returns the number of business days between two dates
@Created	The date a document was created
@Date()	Returns the date component of a time-date value
@Day()	Returns the day of the month of a time-date value
@ Hour ()	Returns the hour component of a time-date value
@IsTime()	Returns True if the value is a time-date.
@Minute()	Returns the minute component of a time-date value
@Modified	A list of dates on which a document was modified
@Month()	Returns the month number of a time-date value
@Now	Returns the current date and time
@Second()	Returns the second component of a time-date value
@Text()	In this context, converts time-date values to text
@TextToTime()	Converts a text representation of a time-date value to a time-date value
@Time()	Returns the time component of a time-date value
@Today	Returns today's date
@Tomorrow	Returns tomorrow's date
@Weekday()	Returns the weekday number of a time-date value
@Year()	Returns the year of a time-date value
@ Yesterday	Returns yesterday's date
@Zone	Returns the time zone component of a time-date value

Phew! There are a lot of @Functions, aren't there? Believe it or not, you will find good uses for many of them. Three of these @Functions are properties of documents: @Accessed , @Created , and @Modified . These contain the last date and time a document was accessed, when it was created, and the dates and times it was modified, respectively. @Now , @Today , @Tomorrow , and @Yesterday all return specific time-date values, as indicated by their names . You use the other @functions to manipulate time and date values in formulas.

Time Date Values

Domino stores both the date and time in Date/Time fields. Similarly, a variable can store both dates and times. A time-date value can contain a date, a time, or a value combining both a date and a time. The function @Date() returns the date component of a time-date value, and @Time returns the time component. The standard format of a time-date value is MM/DD/YYYY HH:MM:SS AM/PM time zone . You can also store just a date or time value in a time field or variable. For more information on the Time field, see Chapter 2, "The Release 6 Object Store." Don't forget that in both view column and field properties, you can elect to display the date and time, the date only, or the time only. You can also affect the format of the display. View columns are covered in Chapter 6, "Designing Views." Field properties are discussed in Chapter 4, "Forms Design."

You can work with the values of the document properties @Created , @Accessed , and @Modified , but you can't modify them. You can, however, use these functions to build document histories. An example is given in the section titled "Getting Session and User Information," later in this chapter.

TIP

If you want to display the date a document was created, you don't need to create a field with document presence, although you can. Simply create a field that is computed for display with a value of @Created . Use the same technique if you want to display the last modification or last access times. There is no reason to take up space in the document with a field to store a value that already exists as a part of the document properties.

 

Working with Dates

Not enough space exists to cover all the @Functions that you can use to work with dates. This section provides some detail on a few of the more useful ones. The function @Adjust() is particularly useful because it enables you to adjust a time-date value in the past or into the future in increments as small as a second. Its syntax is the following:

@Adjust(DateTime; Year; Month; Day; Hour; Minute; Second; [InLocalTime] [InGMT])

The first six parameters after the time-date value are all numeric, and all are required. The last parameter is optional and adjusts the date for daylight saving time. For example, to adjust an invoice due date to 30 days in the future, you can use @Adjust(dDue; 0; 0; 30; 0; 0; 0 ) . To adjust it for one month in the future, you can use the following formula: @Adjust(dDue; 0; 1; 0; 0; 0; 0 ) .

Many time-date @Functions can be particularly useful in views in which you want to sort documents by the date of creation or some other date stored in a field. Figure 12.6 illustrates a frequent use for time-date @Functions. In the first column, the @Function @Year() extracts the year, and the following formula extracts the quarter in the second column:

"Quarter " + @Text(@Integer((@Month(dSales)-1) / 3)+1)

Figure 12.6. This view uses time-date @Functions to produce reports categorized by the year and quarter.

Building on the view shown in Figure 12.6, you can add a month category just underneath the quarter category. To display the text version of the month, you need to work a little harder because no function directly returns July or August . Even if there were such a function, you couldn't sort on that value because you'd end up with an alpha sort, placing April ahead of January. The function @Month() returns the month number, so you can use it to sort the documents by month in a hidden column immediately preceding the alpha month column. Set the @Month() column to Ascending (or Descending) sort, and set the Alpha Month column to sorted and categorized. The following function converts the number of the month to the text equivalent using @Select :

@Select(@Month(dSales); "January"; "February"; "March"; 
 "April"; "May"; "June"; "July"; "August";
 "September"; "October"; "November"; "December")

An alternative formula uses @Word() instead of @Select() , as in the following:

@Word("January February March April May June July August September October November

December"; " "; @Month(dSales));

Adding this to the view produces the result shown in Figure 12.7.

Figure 12.7. A month category is used frequently to display documents in a date-sorted view.

You can control the display format of a field or column that contains a time-date value by using the Control tab of a field properties box and the Date and Time Format tab of a view column properties box. For example, you can display the date component of a view column or field in several different formats, such as MM/DD/YY , MM/YY , MM/YYYY , and so on. You can also use the @Date() function to return just the date portion of a time-date value or @Time() to return the time portion. The function @Date() can also be used to convert numeric values to dates. See the section titled "Converting Time-Date Values," a little later in this chapter, for more information.

Working with Time

You can use several @Functions to extract and display the time component. The function @Time() , like its counterpart @Date() , extracts the time component of a time-date value. This is important because Domino can store both dates and times in the same field or variable.

The functions @Hour() , @Minute() , and @Second() return hours, minutes, and seconds, respectively, from a time-date value.

The function @Now returns the current time-date of the machine on which the formula is executed. @Now evaluates a complete time-date value accurate to the second; it is useful for storing the time of an operation. For example, an application can have agents that run on a schedule or with a modified trigger. You can track the precise time at which the agent runs and store the value in an agent history or document history field using @Now . The following formula converts the current time to a text value and appends a line to the document history:

@SetField("cDocHistory"; cDocHistory : "Notification emailed to " + jcSendTo + " on " +

@Text(@Now; "S2"))

Note the colon between cDocHistory and the text constant. This places the text at the end of the document history field, which is a multivalue field. The resulting text might read Notification emailed to John Smith/IS/MyCompany on 08/16/98 08:48:04 PM . (The @Text() function is discussed in the next section.)

Converting Time-Date Values

Conversion of time-date values typically involves text values. The @Text() function converts time-date values to text. The function @TextToTime() converts a text value to a time-date value. The functions @Date() and @Time() both convert numeric values to time-date values.

The @Text() function is very versatile because of the level of control you can exert over the conversion process. The syntax is the following:

@Text(

time-date; parameters

).

The parameters are listed in Table 12.7 and control the format of the return value.

Table 12.7. Parameters for @Text() Time-Date Conversion

Parameter	Result
General Parameters
S0	Date only
S1	Time only
S2	Date and time
S3	Date and time, plus Today , Yesterday , or Tomorrow
Sx	Used when the format of the value passed can not be predicted
Date Parameters
D0	Year, month, and day
D1	Month and day, plus year if not the current year
D2	Month and day only
D3	Year and month
Time Parameters
T0	Hour, minute, and second
T1	Hour and minute only
Time Zone Parameters
Z0	Converts time to the current time zone
Z1	Displays the zone if not the same as the current time zone
Z2	Always displays the zone

Converting Text to Numbers

@Text() can also be used to convert text to numbers. Unlike its counterpart @TextToNumber() , @Text() enables you to specify the following parameters:

G	General format
F	Fixed
S	Scientific notation
C	Currency
,	Punctuated at thousands
%	Percent
(	Negative numbers in parentheses
n	Digits for precision

Like the format string for time, these can be combined. For example, @Text(105001.056; "F2,") returns 105,001.06 .

You will find many uses for the parameters used to convert time to text. For example, the formula @Text(@Now; "S2") uses the format string "S2" , which returns results such as "05/22/98 04:46:58 PM" . If you want to get rid of the seconds component, you can use @Text(@Now; "S2T1") , which produces "05/22/98 04:46 PM" . Producing just the date can be accomplished as easily with @Text(@Now; "S0") . Note that @Text(@Today) also works.

Of course, you are not restricted to using @Text with time-date @Functions such as @Now . You can also use them with fields or variables that contain time-date values. @Text(dCall; "D0S0") displays the call date ( dCall ) using only the date portion.

To convert a text value into a time-date value, you use @TextToTime( stringvalue ) . Note that stringvalue must be a legitimate time-date string that matches what Domino expects. It can also be a relative date value, such as Tomorrow , Today , or Yesterday . If today is October 15, 2002, @TextToTime("Yesterday") displays 10/14/2002 . However, @TextToTime("October 15, 2002") fails because the string value is not a recognizable date. The expression @TextToTime("10/15/2002") is the correct format. You can also add a time component to the formula, as in the following: @TextToTime("10/15/2002 05:10:10 PM") .

CAUTION

If you fail to include the AM or PM component in a TextToTime() formula or use military time ( 17:10:10 ), the time component defaults to AM .

 

You can use @Date() and @Time() in several ways, in addition to the forms of these commands that extract date or time components from an existing time-date value. Two syntax forms exist for each function. One is extended and includes all time-date components ; the other is a short form that includes the date part for @Date() and the time part for @Time() . The following is the syntax for each form:

@Date( year; month; day ) 

@Date( year; month; day; hour; minute; second )

@Time( hour; minute; second )

@Time( year; month; day; hour; minute; second )

Sometimes you want to convert a date into its "full" version ”that is, November 16, 2002. To do so, use the following formula:

@Select(@Month(@Today);"January";"February";"March";"April"; 
 "May";"June";"July";"August";"September";"October";
 "November";"December")+" "+@Text(@Day(@Today))+ ", "
 +@Text(@Year(@Today))

Converting a date expressed in this fashion back to a valid time-date value is a little trickier. There are several different techniques; the following is one for your review:

REM "cCreated is in a text based Month, day, year format"; 
REM "Trim the value in case the user put extra spaces in there" ;

jcDate := @Trim(cCreatedDate) ;

REM "Find out which month it is using the short three";
REM "character version of the month.";
jcMonthNumber := @If(@Contains(jcDate; "Jan") ; "01";
 @Contains(jcDate; "Feb") ; "02";
 @Contains(jcDate; "Mar") ; "03";
 @Contains(jcDate; "Apr") ; "04";
 @Contains(jcDate; "May") ; "05";
 @Contains(jcDate; "Jun") ; "06";
 @Contains(jcDate; "Jul") ; "07";
 @Contains(jcDate; "Aug") ; "08";
 @Contains(jcDate; "Sep") ; "09";
 @Contains(jcDate; "Oct") ; "10";
 @Contains(jcDate; "Nov") ; "11";
 @Contains(jcDate; "Dec") ; "12";
 "12") ;
REM "Between the first space and the comma is the day";
REM "of the month; the year is whatever is to the";
REM "right of the comma";
jcDay := @Middle(jcDate; " "; ",");
jcYear := @RightBack(jcDate; ", ");

REM "Put the pieces together in a text string and use";
REM "@TextToTime to create a valid time-date value";
@TextToTime(jcMonthNumber + "/" + jcDay + "/" + jcYear)

Working with Date Math

In any programming language, date math can be very tricky. You might expect that @Today - @Yesterday would return 1 because today and yesterday are one day apart. Instead, it returns 86,400 . Remember that number because it is the product of the number of hours in a day multiplied by the number of minutes per hour and then multiplied by the number of seconds per minute, or 24 x 60 x 60. All date math is expressed in seconds, so @Tomorrow - @Yesterday returns 172,800 . This is an improvement over some languages that returned seconds past midnight. That made programming date functions frustrating and tedious .

If you want to know the number of days between two time-date values, simply divide the result by the magic number: 86,400. If you need the result in hours, use 3,600, which is 60 minutes x 60 seconds. Remember that when you do date math and divide by 86,400, the result is the number of days, not the number of days plus the number of hours. For example, consider the following equation:

[02/15/99 05:00 PM] - [02/14/99 05:00 AM]

The previous equation yields 129,600 seconds, or 1.5 when divided by 86,400. If you just need the number of days and don't want the decimal part of the result, combine your formula with @Integer , as in the following example:

@Integer(([02/15/99 05:00:00 PM] - [02/14/99 05:00:00 AM] ) / 86400)

The result of the previous expression is 1 .

Date math can get more complex if you need to express the result in days plus hours. The following example yields "1 day(s) and 12 hour(s)" :

@Text(@Integer( ([02/15/99 05:00:00 PM] - [02/14/99 05:00:00 AM] ) / 86400) ) + " day(s)

and " + @Text((@Modulo( ([02/15/99 05:00:00 PM] - [02/14/99 05:00:00 AM] 86400)) / 3600)

+ " hour(s)"