Exago Logo
Search

More results...

Generic filters
Exact matches only
Request a demo

Date Functions

Date functions can be used to do calculations and formatting on date and time values.
v2019.2.35 v2020.1.18 v2021.1.6 For all of the functions below except for DateCustomFormat() and Time(), passing Null(), DbNull() or any value that resolves to Null() or DbNull(), including null in a database field will return Null(). For example, calling DateAdd("m",3,Null()) returns Null().

Date

Description Creates a date value from three numeric values.
Remark This function should be used to represent a date to other functions instead of representing a date as text.
Example Date(2012,7,4) – returns the date July 4th, 2012.

DateAdd

Description Returns the sum of a date and a quantity of time.
Remark DateAdd takes three input arguments:

  1. A string representing the interval you want to add. The interval can be “yyyy” (year), “y” (days), “d” (days), “w” (weeks), “m” (months), “h” (hours), “n” (minutes), “s” (seconds), “ww” (weeks), or “q” (quarters).
  2. A real number representing how much time you want to add to the date.
  3. A date value.
Example DateAdd('h',1,Now()) — returns the date and time 1 hour from now.
DateAdd('h',-1,Now()) – returns the date and time 1 hour ago.

DateCustomFormat

Description Modifies a date value to be output in a specified format.
Remark Defaults to MM/dd/yy format if no alternative format is specified. Cells that use this function must be formatted as Text.
Example DateCustomFormat(Date(2018,2,24), "MM-dd-yy") — returns the string “02-24-18”.

DateDiff

Description Returns the amount of time between two dates as an integer.
Remark DateDiff takes three input arguments:

  1. A string representing the interval you want to subtract by. The interval can be “yyyy” (year), “d” (days), “w” (weeks), “m” (months), “h” (hours), “n” (minutes), “s” (seconds), “ww” (weeks), or “q” (quarters).
  2. A date value that will be subtracted from the base value.
  3. A base date value.
Example DateDiff('yyyy', Date(1787,9,17), Now()) — returns the number of years since the signing of the United States’ Constitution
DateDiff('h', 14:00, 14:45) — returns 0 (zero), the number of complete hours elapsed during this 45 minute period.

DateValue

Description Converts a date represented as a string (in a .NET DateTime format) to a DateTime for further processing by the application. For example, use this function when comparing two dates.
Remark
  • If the year portion of the argument is omitted, DateValue uses the current year on the server for the year.
  • If the day portion of the argument is omitted, DateValue uses 1 for the day.
  • If the time portion of the argument is omitted, DateValue uses midnight (00:00:00) for the time.
  • If the argument is null, returns null (as notedf above)
Example
  • DateValue("30-jun-2011") — returns a DateTime object 6/30/2011 00:00:00.
  • DateValue("August 8, 2021 1:13 AM") — returns a DateTime object 8/8/2021 01:13:00
  • DateValue({Orders.OrderDate}) > DateValue(Today()) — compares the order date to today.

Day

Description Returns the day portion of a date as a whole number.
Remark Values returned by the YEAR, MONTH and DAY functions will be Gregorian Calendar values regardless of the display format for the supplied date value.
Example Day({Appointment.Date}) – returns the day of the appointment.

DayOfWeekName v2021.1+

Description Returns the name of the weekday of a date (e.g. Monday, Tuesday, Wednesday, … Sunday)
Remark Values returned are referenced from the language file.
Example DayOfWeekName(Date(2019,10,1)) — returns Tuesday since October 1, 2019 occured on a Tuesday.

DayOfWeekNumber v2021.1+

Description Returns the number of the weekday of a date, where 1 = Sunday, 2 = Monday, 3 = Tuesday … 7 = Saturday
Example DayOfWeekName(Date(2019,10,1)) — returns 3 since October 1, 2019 occured on a Tuesday.

Days360

Description Returns the number of days between two dates based on a 360-day year.
Remark Often used in accounting applications.

Days360 takes three input arguments:

  1. The first date value.
  2. The second date value.
  3. Optional: True/False indicating to use European or American method of computation. If not included the American method is used.
Example Days360({Appointment.Date}, Today()) - returns the number of days between today and the appointment date.

GlobalDateFormat

Description Returns a String value whose format is based on the session format.
Remark Only accepts data objects, parameters, and cell references as input.
Example GlobalDateFormat({Appointment.Date}) – returns the date of the appointment based on the session format.

GlobalDateTimeFormat

Description Returns a String value whose format is based on the session format.
Remark Only accepts data objects as input.
Example GlobalDateTimeFormat({Appointment.Date}) – returns the date and time of the appointment based on the session format.

Hour

Description Returns the hour of a time value ranging from 0 (12:00 AM) to 23 (11:00 PM).
Remark Times may be entered as text strings within quotation marks or as a DateTime value.
Example Hour("2:50:05 PM") – returns 14.

Minute

Description Returns the Minute of a time value ranging from 0 to 59.
Remark Times may be entered as text strings within quotation marks or a date time value.
Example Minute("2:50:05 PM") – returns 50.

Month

Description Returns the month portion of a date as a whole number, ranging from 1 (January) to 12 (December).
Remark Values returned by the YEAR, MONTH and DAY functions will be Gregorian Calendar values regardless of the display format for the supplied date value.
Example Month({Appointment.Date}) – returns the month of the appointment.

MonthName v2017.2+

Description Returns the name of the month for a given date.
Remark Accepts a full date or date formatted string, or simply an integer representing the month.
The month name is returned in the current language.
Examples MonthName(2) — returns “February”

MonthName("02/24/1991") — returns “February”

Now

Note

In pre-v2021.2.0, this function returns the current time on the server. In v2021.2.0+, this function returns the current time based on the end user’s Culture Settings in the session.

Description Returns today’s date and time (in local server time).
Remark If embedding in other functions use Now(‘false’).
Example Now() — returns the current date and time.
Now('false') — returns the current date and time formatted mm/dd/yyyy hh:mm:ss

QuarterName v2017.2+

Description Returns the fiscal quarter for a given date, as text from Q1 to Q4.
Remark Administrators can customize this function for different localities.
Example Quarter("02/24/1991") — returns “Q1”

QuarterNumber v2017.2+

Description Returns the fiscal quarter for a given date, as a number from 1 to 4.
Remark Administrators can customize this function for different localities.
Example Quarter("02/24/1991") – returns 1

Second

Description Returns the seconds of a time value ranging from 0 to 59.
Remark When a time omits seconds, 0 (zero) is assumed.

Times may be entered as text strings within quotation marks or a date time value.
Example Second("2:50:05 PM") — returns 5.

Time

Description Returns the number of ticks in a period of hours, minutes and seconds.
Remark This function should be used to represent a time to other functions instead of representing a time as text.

Time takes three input arguments:

  1. Hours
  2. Minutes
  3. Seconds
Example Time(14,50,5) – returns 534050000000.

TimeFormat1

Description Returns the time component of a DateTime nput as a time object.
Remark This function should be used to represent a time to other functions instead of representing a time as text.

Note

The return value of this function should be formatted as text. Cells formatted as General or Date may contain an erroneous placeholder date.
Example TimeFormat1({Appointment.Date}) — returns the time component of the appointment date in the format ‘hh:mm tt’.

TimeValue

Description Convert a time represented in text (i.e., “HH-mm-ss”) into time values that can be passed to other functions.
Remark Acceptable formats include “5:55 PM” and “17:55”. A time separator is mandatory (“17:00” is acceptable, “1700” is not).

If AM/PM is not present AM is assumed. When specifying AM or PM, do not use periods (“A.M.” or “P.M.” will return an error).

Note

The return value of this function should be formatted as text. Cells formatted as General or Date may contain an erroneous placeholder date.
Example TimeValue(Time(14,50,5)) – returns the time object 14:50:05.

Today

Note

In pre-v2021.2.0., this function returns the current date on the server. In v2021.2.0+, this function returns the current date based on the end user’s Culture Settings in the session.

Description Returns today’s date with no time component.
Remark If embedding in other functions use Today(‘false’).

See the Now() function to get today’s date with its time component.
Examples Today() — returns the current date.
Today('false') — returns the current date formatted as mm/dd/yyyy.

WeekOfYear v2021.1+

Description Returns the ordered number of the week in the year that a date occurs on, from 1 to 53.
Example WeekOfYear(Date(2021,05,8)) — returns 19, since May 8, 2021 occurs on the 19th week of the year.

WeekOfYear(“5/8/2021”) — returns 19, since May 8, 2021 occurs on the 19th week of the year.

Year

Description Returns the year portion of a date as a whole number, ranging from 1 to 9999.
Example Year(Today()) — returns 2011.
Was this article helpful?
3.8 out of 5 stars
5 Stars 0%
4 Stars 50%
3 Stars 50%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Table of Contents