Manipulating date-and-time data
Both JavaScript and BIRT provide a wide range of functions for manipulating dates. In the expression builder, look under Native JavaScript Functions—Date, and under BIRT Functions—BirtDateTime. The following sections describe some of the common date-manipulation tasks and provide examples of expressions.
Displaying the current date
A report typically displays the date on which it is generated, so that users can tell if the data in the report is up to date. To display the current date, use one of the following expressions in a data element:
new Date() ) and new Date( ) return the current date and time. ) returns the current date and a time value of midnight, 12:00 AM.
When the report is run, the current date appears in the format that is determined by the locale setting on the user’s system and by the data type you select when you define the data element. For example, if the locale is English (United States) and you select the Date Time data type, the date appears as follows for new Date( ) and ):
Jan 19, 2014 10:30 PM
For ), the date appears as follows:
Jan 19, 2014 12:00 AM
To display only the date portion, use any of the three functions and select the Date data type. The following value appears when you specify the Date data type:
Jan 19, 2014
To display only the time portion, use new Date( ) or ) and select the Time data type. The following value appears when you specify the Time data type:
10:30:45 PM
To display the date in a different format, such as 01/19/09, use the data element’s Format DateTime property to apply the desired format.
Getting parts of a date or time as a number
You can use the JavaScript date functions, such as getDay( ), getMonth( ), and getYear( ), to get the day, month, or year of a specified date field. Similarly, using the getHours( ), getMinutes( ), and getSeconds( ) functions, you can get the hour, minute, or second of a specified time field.
All these functions return values as numbers. For example, getDay(row["orderDate"]) returns 1 for a date that falls on Monday. Except for getDate( ), which returns the day of the month, the range of return values for the other functions start at 0. The return values for getMonth( ), for example, are between 0, for January, and 11, for December. Similarly, getDay( ) returns 0 for Sunday and 6 for Saturday.
To display parts of a date in a different format, for example, display the month as a word such as January, February, and so on, use Property Editor to set the data element’s Format DateTime property to the desired format.
Calculating the time between two dates
It is often useful to calculate and display the number of days, months, or years between two dates. For example, a data source might store two dates for each order record—the date on which the order was placed and the date on which the order was shipped. To provide information about order fulfillment trends, use BIRT’s BirtDateTime functions to calculate and display the number of days between the order date and the ship date, as follows:
BirtDateTime.diffDay(row["orderDate"], row["shippedDate"])
You can also display the number of hours between the two dates, using the following expression:
BirtDateTime.diffHour(row["orderDate"], row["shippedDate"])
Use a different BirtDateTime function, depending on the range of time between two dates. For example, you would not use BirtDateTime.diffMonth( ) to calculate the amount of time between order dates and ship dates because if orders are usually shipped within two weeks, BirtDateTime.diffMonth( ) would often return 0.
Calculating a date
You can add or subtract a specified amount of time to, or from, a date to calculate a new date. For example, the following information is stored for each order record: the date on which the order was placed and the shipment time in days. You want to calculate the date that customers can expect to receive their orders. Given those two fields, calculate the new date by adding the number of shipping days to the date on which the order was placed. Use BIRT’s BirtDateTime.addDay( ) function to calculate the new date. The addDay( ) function takes two arguments: the starting date and the number of days to add.
The following expression shows how to calculate the expected delivery date:
BirtDateTime.addDay(row["orderDate"], row["shipTime"])
You can also calculate a new date by adding a specified number of seconds, minutes, hours, weeks, months, quarters, or years. Use the corresponding add<time period> function. The following expression uses the addMonth( ) function to add two months to each value in the startDate field and return the date:
BirtDateTime.addMonth(row["startDate"], 2)
To subtract a specified amount of time, specify a negative number for the second argument, as shown in the following example:
BirtDateTime.addMonth(row["startDate"], -2)
Using specific dates in an expression
When creating an expression that contains a specific date, you can use any of the following expressions to represent a date:
new Date(2014, 0, 31)
"2014-01-31 15:30:30"
All the expressions represent January 31, 2014. The first expression creates a JavaScript Date object. Months in a JavaScript Date object start at 0, so January is month 0. If you cannot remember the correct date expression to type, use the calendar tool in the expression builder to select a date, as shown in Figure 11‑9. The date you select appears in the following format:
"2014-01-30 11:22:15.872-0800"
Figure 11‑9 Use the calendar tool in the expression builder to select a date
By default, the time portion of the date expression uses the current time, including the milliseconds. The ‑800 part of the expression indicates the time zone in the RFC 822 4‑digit time zone format.
The following examples show expressions that include specific dates. The first expression returns the number of days from the current date to Christmas:
BirtDateTime.diffDay(, "2014-12-25")
The following expression calculates expected delivery dates. If the order date is after December 22, 2014, add the standard shipping time plus three days to the orderDate date; otherwise, add the standard shipping time.
if (row["orderDate"] >= "2014-12-20"){
BirtDateTime.addDay(row["orderDate"], row["ShipTime"] + 3)
BirtDateTime.addDay(row["orderDate"], row["ShipTime"])