Date and Time Functions

Spectre date and time functions manipulate, compare, and perform calculations on date, datetime, period, and time values in expressions. These functions can be used in all Spectre file types.

Note that some functions have different formats, such that the supplied arguments determine the behavior.

Return the age of a person with a specified birth date on a specified day.
age(birthdate : date, date : date) : integer
This function returns 0 if date is before birthdate.

For example:

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

`age(date("1776/07/04"), date("1976/07/04"))` returns 200

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

add "Patient Age" `age(value("Patient Birth Date"), today())` adds a column with the patient's current age

`age(date(datetime("1954/12/04 05:00:00")), date(datetime("2019/03/09 15:07:00")))` returns 64

Convert the integer number of days after 1899/12/30 to a date.
date_from_date_number(date_number : integer) : date

For example:

`date_from_date_number(42301)` returns 2015/10/24

`date_from_date_number(41320)` returns 2013/02/15

Return the number of days between 1899/12/30 and a date.
date_number(date : date) : integer

For example:

`date_number(date("2015/10/24"))` returns 42301

`date_number(date("2008/12/29"))` returns 39811

`date_number(date(datetime("2018/12/29 03:10:27")))` returns 43463

Return the day of the month, which is the day value of yyyy/mm/dd, for a date.
day(calendar: string, date : date) : integer
day(date : date) : integer
day(datetime : datetime) : integer
The day() function is shorthand for extract with interval string set to "day".

For example:

`day("standard", date("2018/10/24"))` returns 24

`day("offset November", date("2017/03/01"))` returns 1

`day(date("2015/10/24"))` returns 24

`day(datetime("2019/12/04 23:20:05"))` returns 4

NOTE: The day() function supports only standard and offset<month> calendars.
Return the day of the week for the day portion of the specified date.
day_name(date : date) : string
The day_name() function is shorthand for format_date with format string of "AAAA". For the abbreviated name, use format_date(date, "AAA")

For example:

`day_name(date("2015/10/24"))` returns Saturday

NOTE: day-name() returns the name in the language configured on the server.

Return, as a string, the elapsed time between two dates and times, or between two datetime elements.
elapsed_time(date1 : string, time1 : string, date2 : string, time2 : string) : string
elapsed_time(date1 : date, time1 : string, date2 : date, time2 : string) : string
elapsed_time(datetime1 : datetime, datetime2 : datetime): string

For example:

`elapsed_time("2000/05/12", "10:25:00", "2000/05/12", "23:30:00")` returns 13:05:00

`elapsed_time("2005/12/31", "23:59:00", "2006/01/01", "00:00:00")` returns 00:01:00

`elapsed_time("2005/07/04", "00:00:00", "2005/07/05", "00:00:00")` returns 24:00:00

`elapsed_time("", " 23:30:00", "", "10:25:00")` returns -13:05:00

`elapsed_time("", " 10:25:00", "", "23:30:00")` returns 13:05:00

`elapsed_time("2006/07/04", "00:00:00", "1776/07/04", "00:00:00")` returns -2016120:00:00

`elapsed_time("1776-07-04", "00:00:00", "2006-07-04", "00:00:00")` returns 2016120:00:00

`elapsed_time(\"2017/01/22\", \"\", \"2017/01/23\", \"14:00:00\")` returns null

`elapsed_time(datetime("1999/12/22 13:23:34"), datetime("1999/12/23 14:24:36"))` returns 25:01:02

`elapsed_time(datetime("2018/12/22 13:23:34"), datetime("2018/12/21 14:24:36"))` returns -22:58:58

add "ED Length of Visit" `elapsed_time(value("ED Arrival Date Time"), value("ED Departure Date Time"))` adds a column with the length of visit

TIP: Because the elapsed_time function requires string processing, it is slower than using elapsed_time_in_seconds.

Return, as an integer, the elapsed time in seconds between two dates and times specified as integers, or between two datetime elements.
elapsed_time_in_seconds(date1 : date, hour1 : integer, minute1 : integer, second1 : integer, date2 : date, hour2 : integer, minute2 : integer, second2 : integer) : integer
elapsed_time_in_seconds(datetime1 : datetime, datetime2 : datetime): integer

For example:

`elapsed_time_in_seconds(date("2000/05/12"), 10, 25, 00, date("2000/05/12"), 23, 30, 00)` returns 47100 seconds (that is 13:05:00).

`elapsed_time_in_seconds(datetime("1999/12/22 13:23:34"), datetime("1999/12/23 14:24:36"))` returns the integer 90062 (that is, 25*60*60 + 1*60 + 2)

`elapsed_time_in_seconds(date("2000/05/11"), 10, 25, 00, date("2000/05/12"), 23, 30, 00)` returns 133500

`elapsed_time_in_seconds(date("2000/05/12"), 10, 25, 00, date("2000/05/11"), 23, 30, 00)` returns -39300

TIP: When practical, use this function rather than the elapsed_time function—the lack of string processing makes it faster.

Return an integral element of a date or period.
extract(calendar : string, date : date, interval : string) : integer
extract(date_or_period : date-or-period, interval : string) : integer
extract(datetime : datetime, interval : string) : integer
The interval string must be one of: "year", "half", "tertile", "quarter", "month", "week", "day", or "year_day". For periods, returns an integer interval number for the specified period value. The interval must be present in the calendar. The calendar argument is optional.

For example:

`extract(value("Last Date", "week") returns the ISO8601 week number for the date in the named column

`extract("offset september", date("2017/09/02"), "year")` returns 2018 because September 2017 is the first month of the year 2018 in the named calendar

`extract("standard", date("2017/09/02"), "year")` returns 2017

`extract("offset November", date("2015/11/30"), "year")` returns 2016

`extract("offset November", date("2015/11/30"), "month")` returns 1

`extract("iso 8601", date("2015/11/30"), "week")` returns 49

`extract(date("2015/10/24"), "year")` returns 2015

`extract(date("2015/10/24"), "month")` returns 10

`extract(period("standard", "year-month", "2017/10"), "year")` returns 2017

`extract(datetime("2018/03/09 01:00:05"), "year")` returns 2018

(Deprecated syntax) `extract(period("gregorian month", "2015/10"), "year")` returns 2015

(Deprecated syntax) `extract(period("gregorian month", "2015/10"), "month")` returns 10

Format a date value with the specified format or the default DI format YYYY/MM/DD.
format_date(calendar : string, date : date, format : string) : string
format_date(date : date, format : string) : string

For example:

`format_date(date("2015/10/24"), "MMM D, YYYY")` returns Oct 24, 2015

`format_date(date("2015/10/24"), "MMMM")` returns October

`format_date(date("2015/10/24"), "AAAA")` returns Saturday

`format_date(date("2015/10/24"), "AAA")` returns Sat

NOTE: The name-producing variants of format-date() return the name in the language configured on the server.

Format a datetime value with the specified format or the default format YYYY/MM/DD hh:mm:ss.
format_datetime(calendar : string, datetime : datetime, format : string) : string
format_datetime(datetime : datetime, format : string) : string

For example:

`format_datetime(datetime("2018/10/24 10:30:05"), "MMM D, YYYY hh:mm")` returns Oct 24, 2018 10:30

`format_datetime(datetime("2019/03/24 23:17:00"), "MMMM ii:mm")` returns March 11:17

dimension "fmt DT" `format_datetime(value("when"),"MMM D, YYYY hh:mm")` returns the dimension value Nov 9, 2017 09:59 if the cBase column when contains the datetime value 2017/11/09 09:59:48

Format a period value with the specified format or the default for the calendar in use.
format_period(period : period, format : string) : string

For example:

`format_period(period("standard", "year-month", date("2017/02/15")), "MMM-YYYY")` returns Feb-2017

`format_period(period("iso 8601", "year-week", date("2016/01/31")), "YYYY!W")` returns 2016!4

(Deprecated syntax) `format_period(period("gregorian month", "2015/10"), "MMM YYYY")` returns Oct 2015

Accept a time string with the specified format and return a standard time string in the format of HH:MM:SS. If the time string is null, return a null string.
format_time(time : string, format : string) : string

For example:

`format_time("10:45", "hh:mm")` returns 10:45:00.

`format_time("65", "mm")` returns 1:05:00.

`format_time("01 01 25 32", "dd hh mm ss")` returns 25:25:32

Format the integer time value and output a string formatted by the optional format string (defaults to HH:MM:SS). Formats without colons, or with other delimiters such as "." or "-" , are acceptable.
format_timevalue(timevalue : integer, format : string) : string

For example:

`format_timevalue(11160, "HH:MM:SS")` returns "03:06:00"

`format_timevalue(11160, "H:MM:SS")` returns"3:06:00"

`format_timevalue(360, "M:SS")` returns "6:00"

`format_timevalue(600, "M:SS")` returns "10:00"

`format_timevalue(6000, "M:SS")` returns "100:00"

`format_timevalue(3723, "H:M:S")` returns "1:2:3"

`format_timevalue(10921, "SS:MM:HH")` returns "01:02:03"

`format_timevalue(-600, "HH:MM:SS")` returns "-00:10:00"

`format_timevalue(11160, "HHMMSS")` returns "030600"

`format_timevalue(11160, "HMMSS")` returns "30600"

`format_timevalue(360, "MSS")` returns "600"

`format_timevalue(10921, "SSMMHH")` returns "010203"

Return the number of hours in a time string or a datetime value.
hours(time : string) : integer
hours(datetime : datetime) : integer

For example:

`hours("20:15:05")` returns 20

`hours(datetime("2019/12/04 23:20:05"))` returns 23

Return the number of minutes in a time string or a datetime value.
minutes(time : string) : integer
minutes(datetime : datetime) : integer

For example:

`minutes("20:15:05")` returns 15

`minutes(datetime("2019/12/04 23:20:05"))` returns 20

Return the month number for a date or period or datetime value.
month(calendar: string, date_or_period : date-or-period) : integer
month(date_or_period : date-or-period) : integer
month(datetime : datetime) : integer
The month() function is shorthand for extract with interval string set to "month".

For example:

`month("standard", date("2017/11/24"))` returns 11

`month("offset November", date("2017/12/01"))` returns 2

`month(date("2015/10/24"))` returns 10

`month(period("standard", "year-month", "2017/08"))` returns 8

(Deprecated syntax) `month(period("gregorian month", "2015/06"))` returns 6

`month(datetime("2019/12/04 12:30:10"))` returns 12

Return the month in the specified date value.
month_name(date : date) : string
The month_name() function is shorthand for format_date with a format string set to "MMMM". For the abbreviated name, use format_date(date, "MMM").

For example:

`month_name(date("2015/10/24"))` returns October

`month_name(date("2015/04/01"))` returns April

`month_name(date(datetime("2019/07/01 09:55:01")))` returns July

NOTE: month-name() returns the name in the language configured on the server.

Return the date and time as it is at the start of the script.
now() : datetime

For example:

`now()` returned 2018/04/24 11:34:21 when running a test script—it always returns the current date and time

add "Timestamp" `now()` creates a new column of type datetime which always has the value of "right now"

add "delta" `elapsed_time(value("Admit Time"), now())` sets the delta column to the difference between the admit time and now, all of which are of type datetime

add "delta seconds" `elapsed_time_in_seconds(value("Admit Time"), now())` sets the delta seconds column to the difference between the admit time in seconds and now in seconds

Shift a date, datetime, or period by a specified number of intervals.
offset(calendar: string, date_or_datetime : date-or-datetime, interval : string, interval_count : integer) : any
offset(period : period, interval : string, interval_count : integer) : any
The interval string must be "year", "half", "tertile", "quarter", "month", "week", "day", "hour", "minute", or "second". For periods, returns the corresponding period the number of intervals after the first day in the period value. The interval must be present in the calendar.

For example:

`offset("standard", date("2017/10/24"), "month", 1)` returns 2017/11/24

`offset(date("2015/10/24"), "month", 1)` returns 2015/11/24

`offset("standard", date("2017/03/29"), "year", -1)` returns 2016/03/29

`offset(date("2015/03/29"), "year", -1)` returns 2014/03/29

`offset(datetime("2019/10/24 12:30:15"), "month", 1)` returns 2019/11/24 12:30:15

`offset(datetime("2019-01-01 03:00:00", "YYYY-MM-DD hh:mm:ss"), "hour", -5)` returns 2018-12-31 22:00:00

`offset(period("year-month", "2020/10"), "month", 1)` returns 2020/11

(Deprecated syntax) `offset(period("gregorian month", "2015/10"), "month", 1)` returns 2015/11

`offset(period("offset June", "year-month", "2021 M10"), "month", 3)` returns 2022 M01

`offset(period("offset March", "year-month", "2021/07", "YYYY/MM"), "month", 2)` returns 2021 M09

Create a period value, either by choosing the period containing the specified date, or by choosing the period represented by the specified string. That is, two variants exist: a date function and a conversion function. The parameters determine which variant applies.
 
The date variant:
Return the period containing a date. Return an error if the date is not present in the specified or default calendar.
period(calendar : string, period-type : string, date : date) : period
period(calendar : string, period-type : string, datetime : datetime) : period
period(period-type : string, date : date) : period
period(calendar-period : string, date : date) : period

For example:

`period("standard", "year", date("2015/11/30"))` returns 2015

`period("standard", "year-tertile", date("2015/11/30"))` returns 2015 T3

`period("standard", "year-quarter", date("2015/11/30"))` returns 2015 Q4

`period("standard", "year-quarter", date(datetime("2018/11/30 06:00:15")))` returns 2018 Q4

`period("standard", "year-quarter", today())` returns the quarter containing today's date; where today is 7/26/2021, returns 2021 Q3

`period("standard", "year-month", date("2015/11/30"))` returns 2015/11

`period("standard", "year", value("Sales Date Time"))` where Sales Date Time is 2019/12/04 08:45:16 returns 2019

`period("offset August", "year-month", value("Ship Date"))` returns a fiscal month value for the ship date; where ship date is 2020/02/10 returns 2020 M07

(Deprecated syntax) `period("gregorian month", date("2015/03"))` returns 2015/03

(Deprecated syntax) `period("gregorian quarter", today())` returns the quarter containing today's date

(Deprecated syntax) `period("bus-cal.cbase", value("Trans Date"))` returns the period for the transaction date as defined in the business calendar

column "YQ" `period("standard", "year-quarter", date(datetime("2020/11/30 06:00:15")))` returns 2020 Q4

column "QYfmt" `period("standard", "year-quarter", date(datetime("2020/11/30 06:00:15")))` format="\\QQ-YYYY" returns Q4-2020

 

The conversion variant:
Parse a string as a period. Converts a formatted period string into a period value. If a format is not specified, the default format for the calendar is used.
period(calendar : string, period-type : string, string : string, format : string) : period
period(period-type : string, string : string, format : string) : period
period(calendar-period : string, string : string, format : string) : period
period(calendar-period : string, string : string) : period

For example:

`period("standard", "year-month", "03-2015", "MM-YYYY")` returns the period value 2015/03

`period("standard", "year-quarter", "2017!2", "YYYY!Q")` returns the period value 2017 Q2

(Deprecated syntax) `period("gregorian month", "03-2015", "MM-YYYY")` returns the period value 2015/03

NOTE:
  • The calendar string can be one of the following: "standard", "offset <month-name>", "iso 8601". The "standard", "offset <month-name>" calendar strings support period types: "year", "year-half", "year-quarter", "year-tertile", and "year-month". The "iso 8601" calendar string supports period types "year" and "year-week". Custom calendar names and period are also supported.
  • The deprecated calendar-period string can be one of the following: "gregorian month", gregorian month with an offset (for example, "gregorian+3 month"), "gregorian quarter", "iso8601 week", or a custom calendar name.
Return the date on which a period ends. Two variants exist as date functions. The parameters determine which variant applies.
 
Return the last day in a period:
period_end(calendar : string, date : date, period : date-or-period) : date
period_end(period : date-or-period) : date

For example:

`period_end(period("standard", "month", "2015/10"))` returns 2015/10/31

(Deprecated syntax) `period_end(period("gregorian month", "2015/10"))` returns 2015/10/31

`period_end(period("standard", "year-month", "2019/08"))` returns 2019/08/31

 
Return the last day in an interval containing a period:
period_end(calendar: string, period : date-or-period, interval : string) : date
period_end(period : date-or-period, interval : string) : date
period_end(datetime : datetime, interval : string) : date
The interval must be one of "year", "half", "tertile", "quarter", "month", "week", or "day".

For example:

`period_end(period("gregorian month", "2015/10"), "quarter")` returns 2015/12/31

`period_end(period("gregorian month", "2015/10"), "month")` returns 2015/10/31

`period_end(date("2017/06/20"), "month")` returns 2017/06/30

`period_end(datetime("2019/10/20 10:30:15"), "quarter")` returns 2019/12/31

 

Return the date on which a period starts. Two variants exist as date functions. The parameters determine which variant applies.
 
Return the first day in a period:
period_start(calendar : string, date : date, period : date-or-period) : date
period_start(period : date-or-period) : date

For example:

`period_start(period("standard", "year-tertile", date("2015/12/11")))` returns 2015/09/01

`period_start(period("offset november", "year-quarter", date("2016/01/31")))` returns 2015/11/01

`period_start(period("gregorian month", "2015/10"))` returns 2015/10/01

 
Return the first day in an interval containing a period or date:
period_start(calendar : string, period : date-or-period, interval : string) : date
period_start(period : date-or-period, interval : string) : date
period_start(datetime : datetime, interval : string) : date
The interval must be one of "year", "half", "tertile", "quarter", "month", "week", or "day".

For example:

`period_start("standard", date("2015/12/11"), "half")` returns 2015/07/01

`period_start("iso 8601", date("2016/01/01"), "week")` returns 2015/12/28

`period_start(period("gregorian month", "2015/07"), "year")` returns 2015/01/01

`period_start(date("2019/12/11"), "tertile")` returns 2019/09/01

`period_start(datetime("2019/12/11 13:45:01"), "half")` returns 2019/07/01

`period_start("standard", date(datetime("2019/12/11 12:00:00")), "quarter")` returns 2019/10/01

 

Return the quarter number for a date or period.
quarter(calendar : string, date_or_period : date-or-period) : integer
quarter(date_or_period : date-or-period) : integer
quarter(datetime : datetime) : integer

For example:

`quarter("standard", date("2017/10/24"))` returns 4

`quarter("offset November", date("2016/02/28"))` returns 2

`quarter(date("2015/10/24"))` returns 4

`quarter("standard", period("year-month", "2015/04"))` returns 2

(Deprecated syntax) `quarter(period("gregorian month", "2015/04"))` returns 2

`quarter("standard", date(datetime("2019/09/26 08:09:10")))` returns 3

`quarter(datetime("2019/01/26 09:16:20"))` returns 1

Create a new period using the same calendar and period type as another period. Two variants exist—parameters determine which variant applies.

 

Return a period value using the same calendar and period type as the specified period, and parsed from the specified string.
same_period(period : period, string : string, format : string) : period

For example:

`same_period(value("YearMo"), "2017/02")` returns the period with the same calendar and period type as YearMo, parsed from the string "2017/02"

`same_period(value("YearMo"), "2017-2", "YYYY-M")` returns the period with the same calendar and period type as YearMo, parsed from the string "2017-2" using the format "YYYY-M"

 

Return a period value that contains the specified date, with the same calendar and period type as the specified period value.
same_period(period : period, date : date) : period

For example:

`same_period(value("YearMo"), date("2017/02/02"))` returns the period with the same calendar and period type as YearMo, containing Feb 2, 2017

`same_period(value("YearMo"), value("Order Date"))` returns the period with the same calendar and period type as YearMo, containing the Order Date

 

TIP: This allows you to create new periods using the same calendar and period type as another period, without needing to fix the calendar or period type names in existing expressions.
Return the number of seconds in a time string or datetime value.
seconds(time : string) : integer
seconds(datetime : datetime) : integer

For example:

`seconds("20:15:05")` returns 5

`seconds(datetime("1954/12/04 20:15:13"))` returns 13

Convert a time string into the number of seconds since midnight. The format argument to indicate how the time string should be parsed is optional—defaults to HH:MM:SS. Formats without colons, or with other delimiters such as "." or "-" , are acceptable.
time_value(time : string, format : string) : integer
timevalue(time : string, format : string) : integer
timevalue(datetime : datetime) : integer

For example:

`timevalue("00:00:20")` returns 20

`time_value("00:05:00")` returns 300

`timevalue("01:00:00")` returns 3600

`timevalue(datetime("2019/03/09 01:00:00"))` returns 3600

`time_value("24:00:00")` returns 86400

`time_value("-13:06:00")` returns -47160

`time_value("13:06", "HH:MM")` returns 47,160

`time_value("6:00", "MM:SS")` returns 360

`time_value("1:2:3", "SS:MM:HH")` returns 10,921

`time_value("-13:06", "HH:MM")` returns-47,160

`time_value("1306", "HHMM")` returns 47,160

`time_value("0600", "MMSS")` returns 360

`time_value("600", "MSS")` returns 360

`time_value("010203", "SSMMHH")` returns 10,921

Return the date (in the default format YYYY/MM/DD) when the script was started.
today() : date

For example:

`today()` returns 2017/08/18 when the script started execution on August 18th, 2017

`today() + 7` returns 2018/02/19 when the script started on February 12, 2018

Return the day of the week for the specified date. Each calendar can define which day of the week is considered the first day.
weekday(calendar : string, date : date) : integer
weekday(date : date, standard : string) : integer

For example:

`weekday("standard", date("2017/02/02"))` returns 5

`weekday("offset november", date("2015/12/12"))` returns 7

`weekday("iso 8601", date("2017/02/02"))` returns 4

`weekday(date("2017/02/02"), "ISO")` returns 4

`weekday(date("2001/09/10"))` returns 2 since September 10, 2001 was a Monday and the standard calendar numbers days from Sunday

`weekday(date(datetime("2001/09/10 07:00:00")))` returns 2

`weekday(date("2000/01/01"), "ISO")` returns 6 since January 1, 2000 was a Saturday and the ISO calendar numbers days from Monday

`weekday(date("1941/12/07"), "")` returns 1 since December 7, 1941 was a Sunday

NOTE: The variant of weekday() which takes a standard as the second argument is for compatibility with Integrator calcs, where you can specify "ISO" for the iso 8601 year-week calendar. The preferred variant is the one which takes the calendar argument first, which is consistent with other functions.

Return the ISO 8601 week date representation for the specified date. See What is ISO 8601?
week_date(date : date) : string
The returned string has the format YYYY-Www-D, where Www is the week number prefixed with 'W' and D is the weekday number.

For example:

`week_date(date("2015/10/24"))` returns 2015-W43-6

`week_date(date("2016/01/01"))` returns 2015-W53-5

`week_date(date(datetime("2019/01/01 09:00:00")))` returns 2019-W01-2

Return the year containing a date or period.
year(calendar : string, date_or_period : date-or-period) : integer
year(date_or_period : date-or-period) : integer
year(datetime : datetime) : integer
The year() function is s horthand for extract with interval string set to "year".

For example:

`year("standard", date("2017/09/02"))` returns 2017

`year("offset November", date("2015/11/30"))` returns 2016

`year("iso 8601", date("2017/01/01"))` returns 2016

`year(date("2015/10/24"))` returns 2015

`year(period("standard", "year-month", "2014/10"))` returns 2014

(Deprecated syntax) `year(period("gregorian month", "2014/10"))` returns 2014

`year("standard", date(datetime("2019/09/02 08:10:20")))` returns 2019

`year(datetime("2019/10/21 12:30:02"))` returns 2019

Return the day of the year (since January first) for a date.
year_day(calendar: string, date : date) : integer
year_day(date : date) : integer
year_day(datetime : datetime) : integer

For example:

`year_day("standard", date("2017/01/01"))` returns 1

`year_day("standard", date("2017/02/12"))` returns 43

`year_day("offset november", date("2015/11/30"))` returns 30

`year_day(date("2015/01/01"))` returns 1

`year_day(date("2015/10/24"))` returns 297

`year_day(datetime("2019/10/21 12:30:02"))` returns 294

See also: