Date and Time Functions

DateAdd

Returns a date to which a specified time interval has been added.

Syntax

DateAdd(interval, number, date)

Arguments

• interval is a string that sets the interval of time you want to add. Possible values are:

  • yyyy - Year

  • q - Quarter

  • m - Month

  • y - Day of year

  • d - Day

  • w - Weekday

  • ww - Week

  • h - Hour

  • n - Minute

  • s - Second

• number is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).

• date is the date to which the interval is added.

Examples

To add one year to the current date, you can use the following expression.

{DateAdd("yyyy", 1, Now())}

DateDiff

Returns the number of time intervals between two specified dates.

Syntax

DateDiff(interval, date1, date2, [ firstdayofweek, [ firstweekofyear ]] )

Arguments

• interval is a string that sets the interval of time you use to calculate the difference between date1 and date2. Possible values are:

  • yyyy - Year

  • q - Quarter

  • m - Month

  • y - Day of year

  • d - Day

  • w - Weekday

  • ww - Week

  • h - Hour

  • n - Minute

  • s - Second

• date1, date2 - two dates you want to use in the calculation.

• firstdayofweek is the optional number that specifies the week's first day (Sunday=1, Saturday=7). If not specified, Sunday is assumed.

• firstweekofyear is the optional number that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs. Possible values are:

  • 1 - start with week in which January 1 occurs

  • 2 - start with the first week that has at least four days in the new year

  • 3 - Start with the first full week of the year.

Examples

To compare how long it took to ship the order after it was placed, you can use the following expression.

{DateDiff("d", ShipDate, OrderDate)}

DatePart

Returns the specified part of a given date.

Syntax

DatePart(interval, date, [ firstdayofweek, [ firstweekofyear ]])

Arguments

• interval is a string that sets is the interval of time you want to return. Possible values are:

  • yyyy - Year

  • q - Quarter

  • m - Month

  • y - Day of year

  • d - Day

  • w - Weekday

  • ww - Week

  • h - Hour

  • n - Minute

  • s - Second

• date - is a date for which you want to return its part.

• firstdayofweek is the optional number that specifies the week's first day (Sunday=1, Saturday=7). If not specified, Sunday is assumed.

• firstweekofyear is the optional number that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs. Possible values are:

  • 1 - start with week in which January 1 occurs

  • 2 - start with the first week that has at least four days in the new year

  • 3 - Start with the first full week of the year.

Examples

You can use the following expression to show the copyright symbol along with the current year.

© {DatePart("yyyy", Now())}

DateSerial

Returns a date for a specified year, month, and day.

Syntax

DateSerial(year, month, day)

Arguments

• year - an integer number between 100 and 9999.

• month - an integer number between 1 and 12

• day - an integer number between 1 and 31

Examples

You can use the following expression to construct January 4, 1982

{DateSerial(1982, 1, 4)}

DateString

Returns a string value representing the current date in yyyy-mm-dd format.

Syntax

DateString()

DateValue

Returns a date from a specified string.

Syntax

DateValue(date)

Arguments

• date - a string that contains a date in yyyy-mm-dd format.

Examples

You can use the following expression to construct a date from 2020-02-29 string.

{DateValue("2020-02-29")}

Day

Returns a number between 1 and 31, inclusive, representing the day of the month.

Syntax

Day(date)

Arguments

• date - a date for which you want to return its day of the month

Examples

You can use the following expression to return the day of the month for the current date.

{Day(Now())}

Hour

Returns a number between 0 and 23, inclusive, representing the hour of the day.

Syntax

Hour(date)

Arguments

• date - a date for which you want to return its hour of the day

Examples

You can use the following expression to return the hour of the day for the current date.

{Hour(Now())}

Minute

Returns a number between 0 and 59, inclusive, representing the minute of the hour.

Syntax

Minute(date)

Arguments

• date - a date for which you want to return its minute of the hour

Examples

You can use the following expression to return the minute of the hour for the current date.

{Minute(Now())}

Month

Returns a number between 1 and 12, inclusive, representing the month of the year.

Syntax

Month(date)

Arguments

• date - a date for which you want to return its month of the year

Examples

You can use the following expression to return the month of the year for the current date.

{Month(Now())}

MonthName

Returns a string indicating the specified month.

Syntax

MonthName(month, [ abbreviate ])

Arguments

• month - the numeric designation of the month. For example, January is 1, February is 2, and so on.

• abbreviate - the optional boolean value that indicates whether the month name should be abbreviated. The default is false

Examples

You can use the following expression to return an abbreviation of the current month.

{MonthName(Month(Now()), true)}

Now

Returns the current date and time.

Syntax

Now()

Second

Returns a number between 0 and 59, inclusive, representing the second of the minute.

Syntax

Second(date)

Arguments

• date - a date for which you want to return its second of the minute

Examples

You can use the following expression to return the second of the minute for the current date.

{Second(Now())}

TimeOfDay

Returns a date containing the current time of day.

Syntax

TimeOfDay()

Timer

Returns the number of seconds elapsed since midnight.

Syntax

Timer()

TimeSerial

Returns a date containing the time for a specific hour, minute, and second.

Syntax

TimeSerial(hour, minute, second)

Arguments

• hour - an integer number between 0 and 23.

• minute - an integer number between 0 and 59

• second - an integer number between 0 and 59

Examples

You can use the following expression to construct a date at 3.30 pm time.

{TimeSerial(15, 30,0)}

TimeString

Returns a string value representing the current time of day.

Syntax

TimeString()

TimeValue

Returns a time value from a specified string.

Syntax

TimeValue(time)

Arguments

• time - a string that contains a time value for a 12-hour or 24-hour clock.

Examples

You can use the following expression to construct a time from 3:15 am string.

{TimeValue("3:15am")}

Today

Returns the current date at midnight.

Syntax

Today()

Weekday

Returns a number representing the day of the week.

Syntax

Weekday(date, [ firstdayofweek ])

Arguments

• date - a date for which you want to return its day of the week

• firstdayofweek is the optional number that specifies the week's first day (Sunday=1, Saturday=7). If not specified, Sunday is assumed.

Examples

You can use the following expression to return the current weekday.

{Weekday(Today())}

WeekDayName

Returns a string indicating the specified day of the week.

Syntax

WeekdayName(weekday, [abbreviate, [firstdayofweek ]])

Arguments

• weekday - the numeric designation for the day of the week. The numeric value of each day depends on the setting of the firstdayofweek argument.

• abbreviate - the optional boolean value that indicates whether the weekday name should be abbreviated. The default is false

• firstdayofweek is the optional number that specifies the week's first day (Sunday=1, Saturday=7). If not specified, Sunday is assumed.

Examples

You can use the following expression to return an abbreviation of the current day of the week.

{WeekDayName(Weekday(Now()), true)}

Quarter

Returns a number between 1 and 4, inclusive, representing the quarter of the year.

Syntax

Quarter(date)

Arguments

• date - a date for which you want to return its quarter of the year

Examples

You can use the following expression to return the quarter of the year for the current date.

{Quarter(Now())}

QuarterName

Returns a string representing the quarter name.

Syntax

QuarterName(date)

Arguments

• date - a date for which you want to return its quarter name

Examples

You can use the following expression to return the quarter name for the current date.

{QuarterName(Now())}

Year

Returns a number representing the year.

Syntax

Year(date)

Arguments

• date - a date for which you want to return its year

Examples

You can use the following expression to return the current year.

{Year(Now())}

AddYears

Returns a new date that adds the specified number of years to the specified date.

Syntax

AddYears(date, years)

Arguments

• date - a date to which you want to add years

• years - number of years to add. It can be negative or positive.

Examples

You can use the following expression to return the date that two years ahead of the current date.

{AddYears(Now(), 2)}

AddMonths

Returns a new date that adds the specified number of months to the specified date.

Syntax

AddMonths(date, months)

Arguments

• date - a date to which you want to add months

• months - number of months to add. It can be negative or positive.

Examples

You can use the following expression to return the date that five months behind the current date.

{AddMonths(Now(), -5)}

AddDays

Returns a new date that adds the specified number of days to the specified date.

Syntax

AddMonths(date, days)

Arguments

• date - a date to which you want to add days

• days - number of days to add. It can be negative or positive.

Examples

You can use the following expression to return tomorrow's date.

{AddDays(Now(), 1)}

AddHours

Returns a new date that adds the specified number of hours to the specified date.

Syntax

AddHours(date, hours)

Arguments

• date - a date to which you want to add hours

• hours - number of hours to add. It can be negative or positive.

Examples

You can use the following expression to return yesterday's date.

{AddHours(Now(), -24)}

AddMinutes

Returns a new date that adds the specified number of minutes to the specified date.

Syntax

AddMinutes(date, minutes)

Arguments

• date - a date to which you want to add minutes

• minutes - number of minutes to add. It can be negative or positive.

Examples

You can use the following expression to return the date that 30 minutes behind the current date.

{AddMinutes(Now(), -30)}

AddSeconds

Returns a new date that adds the specified number of seconds to the specified date.

Syntax

AddSeconds(date, seconds)

Arguments

• date - a date to which you want to add seconds

• seconds - number of seconds to add. It can be negative or positive.

Examples

You can use the following expression to return the date that 45 seconds ahead of the current date.

{AddSeconds(Now(), 45)}

AddMilliseconds

Returns a new date that adds the specified number of milliseconds to the specified date.

Syntax

AddMilliseconds(date, milliseconds)

Arguments

• date - a date to which you want to add milliseconds

• milliseconds - number of milliseconds to add. It can be negative or positive.

Examples

You can use the following expression to return the date that 654 milliseconds behind the current date.

{AddMilliseconds(Now(), -654)}