Date Functions in Integrator

Returns the age of a person with the given birthdate, on date. If date is omitted, the current date is used. If birthdate is later than date, this function returns 0. If birthdate or date contains an invalid date, this function returns 0.

For example:

age("1935/01/08", "2003/01/01") returns 67

age("1776-07-04", "1976-07-04") returns 200

age("1965/02/01") returns 47 (when run in December 2012)

age("2004/12/25", "2003/03/03") returns 0

Performs an arithmetic function on a date by adding or subtracting a number of days or periods, and can optionally return the first or last day of the given period. Period can be one of the following: "day", "days", "week", "weeks", "month", "months", "quarter", "quarters", "year", or "years". If omitted, period defaults to "day". Position can be one of the following: "first", "current", or "last". If omitted, position defaults to "current".

This function adds the specified number of periods to the specified date.

• If position is "current", it returns the resulting date.

• If position is "first", it returns the first day of the given period that contains that date.

• If position is "last", it returns the last day of the given period that contains that date.

  Weeks are defined to begin on Sunday.

• If calculations involving months result in a date that is beyond the end of a month, the last day of the month is returned instead.

date_offset(date : date, number : numeric) : date

date_offset(date : date, number : numeric, period : string) : date

date_offset(date : date, number : numeric, period : string, position : string) : date

For example:

date_offset("2001/09/08", 3) returns 2001/09/11

date_offset("2001/09/08", 3, "days") returns 2001/09/11

date_offset("1985/11/05", -30, "years") returns 1955/11/05

date_offset("2000/02/29", 1, "year", "current") returns 2001/02/28

date_offset("2000/02/29", 1, "year", "first") returns 2001/01/01

date_offset("2001/09/11", 0, "week", "first") returns 2001/09/09

date_offset("2001/09/11", 1, "week", "last") returns 2001/09/22

date_offset("2003-01-15", 12, "months") returns 2004/01/15

date_offset("2013/01/15", 12, "months", "last") returns 2014/01/31

date_offset("2013/01/15", -4, "quarter", "last") returns 2012/03/31

date_offset("2013/03/31", 1, "month") returns 2013/04/30

Accepts a date, in the ISO or DI standard date format, and returns the number of days since December 30, 1899. Dates before this date are represented as negative numbers, and results are valid for any date from January 1, 1753 onward. One date may be subtracted from another to find the time between them, by subtracting the date_value of one from the date_value of the other. If date is an empty string, this function returns 0. If date contains an invalid date, this function returns -3650000.

NOTE: datevalue, along with the mod function, can also be used to determine the day of the week. If you mod a date integer by 7, zero is Saturday, 1 is Sunday, 2 is Monday, etc. For example: if(mod(date_value(date),7)<=1,\"weekend\",\"weekday\")

date_value(date : date) : numeric

datevalue(date : date) : numeric

For example:

datevalue("1899/12/31") returns 1

date_value("1900-07-04") returns 186

datevalue("2006/10/26") - datevalue("1954/12/04") returns 18954

(date_value("2006/10/26") - date_value("1954/12/04"))/365 returns 51.928767

Returns the two-digit day portion of date which must be in the standard ISO or DI date format. If date is null, a null string is returned. If date is invalid, an invalid date is returned.

day(date : date) : numeric

For example:

day("1954/12/04") returns 04

day("1955-10-21") returns 21

day("2001-07-07") returns 07

day("2005/05/55") returns 55

Returns the day of the week name for the given date. The language argument is a two-letter code identifying the language. If it is omitted or blank, the day is returned in English ("Monday", "Tuesday", etc). Supported languages include:

• da—Danish

• de—German

• en—English

• es—Spanish

• fr—French

• it—Italian

• nl—Dutch

• pt—Portuguese

• sv—Swedish

day_name(date : date) : string

day_name(date : date, language : string) : string

For example:

day_name("2001/09/10") returns "Monday"

day_name("2000/01/01", "fr") returns "Samedi"

day_name("1941/12/07", "es") returns "Domingo"

Takes a date, in different formats (there are some limitations), and a string describing that format, and returns the same date in DI’s standard date format which is YYYY/MM/DD. Sorting records in date order is possible using the standard date format. (See also format_date_range.) If date is null, a null string is returned. If date is invalid, an invalid date is returned (return value contains all zeros).

format_date(date : string, format : string) : date

For example:

format_date("3-17-94","mm-dd-yy") returns 1994/03/17

format_date("12/03/07","mm/dd/yy") returns 1907/12/03

format_date("551021","yymmdd") returns 1955/10/21

format_date("090235","yymmdd") returns 1909/02/00

format_date("02312009","mmddyyyy") returns 2009/02/31

format_date("","yymmdd") returns null

format_date("Elvis","yymmdd") returns 0000/00/00

format_date("15-15-2015","mm-dd-yyyy") returns 2015/00/15

Accepts two-digit years more flexibly than format_date and returns the same date in DI’s standard date format YYYY/MM/DD. Two-digit years are interpreted as being in a 100 year range starting from range-start. If date is null, a null string is returned. If date is invalid, an invalid date is returned (return value partially contains zeros).

format_date_range(date : string, format : string, range-start : numeric) : date

For example:

format_date_range("3-17-49", "mm-dd-yy", 1950) returns 2049/03/17

format_date_range("1-27-55", "mm-dd-yy", 1950) returns 1955/01/27

format_date_range("07-12-20", "yy-mm-dd", 1950) returns 2007/12/20

format_date_range("760704", "yymmdd", 1700) returns 1776/07/04

format_date_range("090231", "yymmdd", 2000) returns 2009/02/31

format_date_range("090235", "yymmdd", 1950) returns 2009/02/00

format_date_range("Elvis", "yymmdd", 1950) returns 2000/00/00

format_date_range("15-15-15", "mm-dd-yy", 1900) returns 1915/00/15

format_date_range("", "yymmdd", 1950) returns null

Puts the calculated datevalue back into the standard DI date format. See the date_value function.

format_date_value(datevalue : numeric) : date

For example:

format_date_value(1) returns 1899/12/31

format_date_value(186) returns 1900/07/04

Returns the number representing the day of the year for date between 1 and 365 or 366. If date is null, a null string is returned. If date is invalid, an invalid date is returned.

julian_day(date : date) : numeric

For example:

julian_day("2007/01/15") returns 15

julian_day("2007-12-04") returns 338

julian_day("2005-10-01") returns 274

julian_day("2006/01/26") returns 26

Returns the date for a Meditech time expressed in 9 digit format. The input is the number of seconds from the beginning of March 1, 1980. This function returns the date in standard DI format (YYYY/MM/DD).

meditech_date(meditech_time : numeric) : date

For example:

meditech_date(013817501) returns 1980/08/07

meditech_date(891795289) returns 2008/06/03

Returns the two-digit month portion of the date, which must be in the standard ISO or DI date format. If date is null, a null string is returned. If date is invalid, an invalid date is returned.

month(date : date) : numeric

For example:

month("2007/09/26") returns 09

month("1977-11-07") returns 11

month("2005/15/22") returns 15

Returns the month name of the given date in the specified language. If language is not specified, the month is returned in English. Supported languages include:

• da—Danish

• de—German

• en—English

• es—Spanish

• fr—French

• it—Italian

• nl—Dutch

• pt—Portuguese

• sv—Swedish

month_name(date : date) : string

month_name(date : date, language : string) : string

For example:

month_name("1776/07/04") returns July

month_name("2008-05-24") returns May

month_name("2013-07-15", "fr") returns juillet

Performs an arithmetic function on a month, similar to the date_offset function, by adding or subtracting a number of months. Month can be in the form of YYYY/MM, YYYY-MM, or YYYYMM. The return value will be in the same form as the input month.

month_offset(month : date, number : numeric) : date

For example:

month_offset("2001/09",10) returns 2002/07

month_offset("200705",-1) returns 200704

month_offset("2007-05",36) returns 2010-05

Returns the one-digit calendar quarter in which date occurs. January through March is the first quarter, April through June is the second quarter, July through September is the third quarter, and October through December is the fourth quarter. If date is null, a null string is returned. If date is invalid, an invalid date is returned.

quarter(date : date) : numeric

For example:

quarter("2007/10/05") returns 4

quarter("2010-01-01") returns 1

Returns the date when a specified calculated object starts in standard date format (YYYY/MM/ DD).

Returns the ISO 8601 week date representation for the given date. The returned string will have the format YYYY-Www-D, where Www is the week number prefixed with 'W' and D is the weekday number.

week_date(date : date) : string

For example:

week_date("2011/01/01") returns "2010-W52-6"

week_date("2011/05/01") returns "2011-W17-7"

week_date("2011-12-31") returns "2011-W52-6"

week_date("2008-12-31") returns "2009-W01-3"

week_date("2009-01-01") returns "2009-W01-4"

See What is ISO 8601?

Returns the day of the week for the given date, with the numbering based on a given standard. If standard is omitted or blank, then the days of the week are returned using default Excel values: a Sunday is returned as 1, Monday is 2, ..., and Saturday is 7. If standard has the value "ISO", then the week day number is returned based on the ISO standard: Monday is 1, Tuesday is 2, and Sunday is 7.

weekday(date : date) : numeric

weekday(date : date, standard : string) : numeric

For example:

weekday("2001/09/10") returns 2 (Monday)

weekday("2000/01/01", "ISO") returns 6 (Saturday)

weekday("1941/12/07", "") returns 1 (Sunday)

Returns the four-digit year portion of date.

year(date : date) : numeric

For example:

year("2008/10/04") returns 2008

year("2020-01-01") returns 2020

year("1966/13/13") returns 1966