Function Reference

Function Syntax

To call a function, prefix the name with the @ symbol:

@now("yyyy-MM-dd",-1,"day")

Argument Types

For string arguments, the argument value must be enclosed in quotes. Other argument types do not require quotes.

If you are using a variable or another function as an argument, do not use quotes. For example: @now(${StandardDateFormat}) (assuming you have defined a variable named "StandardDateFormat" that returns a valid date/time formatting string).

Optional Arguments

In the function reference below, brackets [] around an argument indicate that the argument is optional.

If you omit an optional argument but need to specify a subsequent argument, you include the comma after the place where the omitted argument would be. For example, in the EndOfDay function, the first argument (targetDate) is optional; if you omit it, the current date is used. So you would call @EndOfDay(,"yyyy-MM-dd HH:mm"). If you omit multiple arguments, there will be a comma for each skipped argument.

If you omit arguments and don't need to specify subsequent arguments, no comma is needed. For example, if you want to call the now function and specify a format but not an offset, you can call @now("yyyy-MM-dd") rather than @now("yyyy-MM-dd",,).

Note that the parentheses for the function call are always required, even if you are not supplying any arguments. So @EndOfDay() is valid, but @EndOfDay is not

Combining Functions

Function calls can use variables and other function calls as arguments. For example, this call returns the beginning of the month in which yesterday occurs:

Date/Time Format

All of the date/time functions return the date/time formatted as yyyy-MM-dd HH:mm:ss by default. All of the functions accept an optional final argument for specifying a formatting pattern. Calling @EndOfMonth(,"MMMM d, yyyy") is equivalent to calling @FormatDate(@EndOfMonth(),"MMMM d, yyyy").

If you are going to use the result of the function as an argument to another function, you should leave the value in the default format.

Function List

Name	Description
AdjustDate	Adjusts parts of a date/time value
DateAdd	Adds or subtract time increments from a date/time value.
EndOfDay	Gets a date/time value representing 11:59:59 PM on the targetDate.
EndOfMonth	Gets a date/time value representing 11:59:59 on the last day of the month in which targetDate occurs.
EndOfQuarter	Gets the date of the end of the calendar quarter containing a specified date.
EndOfYear	Gets a date/time value representing 11:59:59 on the last day of the year in which targetDate occurs.
FormatDate	Formats a date/time value
MakeDate	Makes a date/time value from years, months, days, hours, minutes, and seconds
MostRecentDay	Gets the most recent occurrence of the specified day of the week, on or before the targetDate.
NextDay	Gets the next occurrence of the specified day of the week, on or after the targetDate.
now	Returns current date/time, with optional format and offset.
QuarterNumber	Gets the number of the calendar quarter (1-4) in which the specified date occurs.
StartOfDay	Gets a date/time value representing 12:00 AM on the targetDate.
StartOfMonth	Gets a date/time value representing 12:00 AM on the first day of the month in which targetDate occurs.
StartOfQuarter	Gets the date of the beginning of the calendar quarter containing a specified date.
StartOfYear	Gets a date/time value representing 12:00 AM on the first day of the year in which targetDate occurs.

Function Reference

AdjustDate

Adjusts parts of a date/time value

Syntax: @AdjustDate([targetDateTime], [year], [month], [day], [hour], [minute], [second], ["format"])

Arguments

targetDateTime

(DateTime) Optional. The date/time value to adjust. If not specified, the current date/time is used.

year

(Integer) Optional. The year value to use. If not specified, the value from the target date/time is used.

month

(Integer) Optional. The month value to use. If not specified, the value from the target date/time is used.

day

(Integer) Optional. The day value to use. If not specified, the value from the target date/time is used.

hour

(Integer) Optional. The hour value to use. If not specified, the value from the target date/time is used.

minute

(Integer) Optional. The minute value to use. If not specified, the value from the target date/time is used.

second

(Integer) Optional. The second value to use. If not specified, the value from the target date/time is used.

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

This function can be used to change any of the parts (year, month, day, hour, minute, second) of a date. For example, if you need a date/time value representing 0 minutes past the current hour, you can use:

@AdjustDate(,,,,,0,0)

The omitted arguments tell the function to use the current date/time as the starting point, and retain the year, month, day, and hour values, but set the minute and second to 0.

Note that you can accomplish the same thing as AdjustDate using the now function by using literal values in the formatting string.

@now("yyyy-MM-dd HH:00:00")

However, AdjustDate is preferred if the result is being used as an argument to another function or as a report parameter.

DateAdd

Adds or subtract time increments from a date/time value.

Syntax: @DateAdd([value], offset, unit, ["format"])

Arguments

value

(DateTime) Optional. Date/time value to adjust. This can come from another function that returns a date/time value, or a Variable that provides a date/time value. If not specified, the current date/time is used.

offset

(Integer) Required. Number of units to add or subtract from current date/time

unit

(Choice) Required. Unit to add or subtract. Valid values:

• year
• month
• month
• day
• hour
• minute
• second

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

EndOfDay

Gets a date/time value representing 11:59:59 PM on the targetDate.

Syntax: @EndOfDay([targetDate], ["format"])

Arguments

targetDate

(DateTime) Optional. The day to return the end of. If omitted, the current date is used.

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

EndOfMonth

Gets a date/time value representing 11:59:59 on the last day of the month in which targetDate occurs.

Syntax: @EndOfMonth([targetDate], ["format"])

Arguments

targetDate

(DateTime) Optional. The day to return the end of the month for. If omitted, the current date is used.

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

EndOfQuarter

Gets the date of the end of the calendar quarter containing a specified date.

Syntax: @EndOfQuarter([targetDate], [month], ["format"])

Arguments

targetDate

(DateTime) Optional. The date/time to find the quarter for. If omitted, the current date/time is used.

month

(Choice) Optional. The month that begins the first quarter of the year. If omitted, January is used.Valid values:

• january
• february
• march
• april
• may
• june
• july
• august
• september
• october
• november
• december

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

EndOfYear

Gets a date/time value representing 11:59:59 on the last day of the year in which targetDate occurs.

Syntax: @EndOfYear([targetDate], ["format"])

Arguments

targetDate

(DateTime) Optional. The day to return the end of the year for. If omitted, the current date is used.

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

FormatDate

Formats a date/time value

Syntax: @FormatDate(value, "format")

Arguments

value

(DateTime) Required. Date/time value to format. This can come from another function that returns a date/time value, or a Variable that provides a date/time value

format

(String) Required. Formatting pattern to use. See the Date/Time Formats help topic for more information.

MakeDate

Makes a date/time value from years, months, days, hours, minutes, and seconds

Syntax: @MakeDate([year], [month], [day], [hour], [minute], [second], ["format"])

Arguments

year

(Integer) Optional. The year value to use. If not specified, the value from the current date/time is used.

month

(Integer) Optional. The month value to use. If not specified, the value from the current date/time is used.

day

(Integer) Optional. The day value to use. If not specified, the value from the current date/time is used.

hour

(Integer) Optional. The hour value to use. If not specified, the value from the current date/time is used.

minute

(Integer) Optional. The minute value to use. If not specified, the value from the current date/time is used.

second

(Integer) Optional. The second value to use. If not specified, the value from the current date/time is used.

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

Create a date/time where the month and day are coming from variables for some reason:

@MakeDate(2015,${MonthToUse},${DayToUse),8,15,0)

MostRecentDay

Gets the most recent occurrence of the specified day of the week, on or before the targetDate.

Syntax: @MostRecentDay([targetDate], day, ["format"])

Arguments

targetDate

(DateTime) Optional. The starting date/time. If omitted, the current date/time is used.

day

(Choice) Required. The name of the day of the week to look for.Valid values:

• sunday
• monday
• tuesday
• wednesday
• thursday
• friday
• saturday

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

This function can be used to find the beginning of the week in which a date occurs. For example, if you define the first day of the week as Monday, you can call this function to get the start of the current week:

@MostRecentDay(${now},"monday")

The targetDate argument is optional, so you could also use

@MostRecentDay(,"monday")

to produce the same result

NextDay

Gets the next occurrence of the specified day of the week, on or after the targetDate.

Syntax: @NextDay([targetDate], day, ["format"])

Arguments

targetDate

(DateTime) Optional. The starting date/time. If omitted, the current date/time is used.

day

(Choice) Required. The name of the day of the week to look for.Valid values:

• sunday
• monday
• tuesday
• wednesday
• thursday
• friday
• saturday

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

This function can be used to find the end of the week in which a date occurs. For example, if you define the last day of the week as Friday, you can call this function to get the end of the current week:

@NextDay(${now},"friday")

now

Returns current date/time, with optional format and offset.

Syntax: @now(["format"], [offset], [unit])

Arguments

format

(String) Optional. Formatting pattern to use. See the Date/Time Formats help topic for more information.

offset

(Integer) Optional. Number of units to add or subtract from current date/time

unit

(Choice) Optional. Unit to add or subtract. Valid values:

• year
• month
• month
• day
• hour
• minute
• second

The now function returns the current date/time, or a date/time based on the current date/time, optionally using a specific format.

Examples

@now()

Inserts the current date/time in the standard format "yyyy-MM-dd HH:mm:ss". Equivalent to using ${now}

@now("MMMM d, yyyy")

Inserts the current date/time using the long date format, e.g., "January 3, 2015."

@now("MMMM d, yyyy",-1,day)

Subtracts 1 day from the current date/time and inserts the result using the long date format, e.g., "January 3, 2015."

QuarterNumber

Gets the number of the calendar quarter (1-4) in which the specified date occurs.

Syntax: @QuarterNumber([targetDate], [month])

Arguments

targetDate

(DateTime) Optional. The date/time to find the quarter for. If omitted, the current date/time is used.

month

(Choice) Optional. The month that begins the first quarter of the year. If omitted, January is used.Valid values:

• january
• february
• march
• april
• may
• june
• july
• august
• september
• october
• november
• december

StartOfDay

Gets a date/time value representing 12:00 AM on the targetDate.

Syntax: @StartOfDay([targetDate], ["format"])

Arguments

targetDate

(DateTime) Optional. The day to return the start of. If omitted, the current date is used.

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

StartOfMonth

Gets a date/time value representing 12:00 AM on the first day of the month in which targetDate occurs.

Syntax: @StartOfMonth([targetDate], ["format"])

Arguments

targetDate

(DateTime) Optional. The day to return the start of the month for. If omitted, the current date is used.

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

StartOfQuarter

Gets the date of the beginning of the calendar quarter containing a specified date.

Syntax: @StartOfQuarter([targetDate], [month], ["format"])

Arguments

targetDate

(DateTime) Optional. The date/time to find the quarter for. If omitted, the current date/time is used.

month

(Choice) Optional. The month that begins the first quarter of the year. If omitted, January is used.Valid values:

• january
• february
• march
• april
• may
• june
• july
• august
• september
• october
• november
• december

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.

StartOfYear

Gets a date/time value representing 12:00 AM on the first day of the year in which targetDate occurs.

Syntax: @StartOfYear([targetDate], ["format"])

Arguments

targetDate

(DateTime) Optional. The day to return the start of the year for. If omitted, the current date is used.

format

(String) Optional. Optional formatting pattern to use for the returned value. Not necessary if the returned value is being used by another date/time function.