Conversion Functions

Spectre conversion functions convert one data type to another data type. These functions can be used in all Spectre file types.

Return a date value. Three variants exist: conversion of a string, conversion of integers to a date, or conversion of a datetime. The parameters determine which variant applies.
Return a date value. Four variants exist: conversion of a string, conversion of integers to a date, conversion of a datetime, or conversion of a date to a date. The parameters determine which variant applies.
 
Parse a string as a date. For more information, see Date Data Type
date(string : string, format : string) : date
If format is null, the function uses the default DI date format YYYY/MM/DD.

For example:

`date("12-04-2015", "MM-DD-YYYY")` returns 2015/12/04

`date("2016/01/01")` returns 2016/01/01

`date("2017/01/31") - date("2017/01/01")` returns 30

`date("2017/01/31") - 5 )` returns 2017/01/26

`date("2017/01/31") + 5 )` returns 2017/02/05

`date("122015", "MMYYYY")` returns "Date error: No day element"

`date("Mar 11, 2013", "MMM D, YYYY")` returns the date value 2013/03/11

`10 + date("2018/01/01")` returns 2018/01/11

`10 - date("2018/01/01")` returns 2017/12/22

`date("2018/01/01") - date("2019/01/01")` returns -365

 

Combine integers as a date. Year, month, and day are assumed to be Gregorian.
date(year: integer, month: integer, day: integer) : date

For example:

`date(2017, 11, 02)` returns 2017/11/02

 

Convert a datetime element into a date data type.
date(datetime()) : date

For example:

`date(datetime("2016/12/22 13:23:34"))` creates the date representing December 22, 2016

 
Convert a date element into a date data type. 
date(date()) : date

For example:

`date(date("2016/12/22"))` creates the date representing December 22, 2016

Return a datetime element for the specified string or for the specified date and seconds. Seconds are optional. For more information, see Datetime Data Type.
datetime (date_time: string) : datetime
datetime (date: date, time: integer) : datetime
datetime (date_time: string, format: string) : datetime
datetime (date_time: datetime) : datetime

The format is "MM/DD/YYYY hh:mm:ss". See the datetime section in Spectre Format Conventions for details.

For example:

`datetime("1999/12/22 13:23:34")` creates the datetime representing December 22, 1999 at 1:23:34 PM

`datetime(date("1999/12/22"), time_value("13:23:34")))` creates the datetime representing December 22, 1999 at 1:23:34 PM

`datetime("12/22/2017 34:23:13", "MM/DD/YYYY ss:mm:hh")` creates the datetime representing December 22, 2017 at 1:23:34 PM

`datetime(date("1999/12/31"), 100000)` produces the timestamp 2000/01/01 03:46:60

`datetime(date("2000/01/01"), -60)` produces the timestamp 1999/12/31 23:59:00

(Deprecated syntax) `datetime("12/22/2017 34:23:13", "%m/%d/%Y %S:%M:%H")` creates the datetime representing December 22, 2017 at 1:23:34 PM

Convert a numeric value to a double. For more information, see Double Data Type.
double(number : numeric) : double

For example:

`double(1)` returns 1.0.

Convert a numeric value to a fixed100 value, truncating precision if necessary. For more information, see Fixed100 Data Type.
fixed100(number : numeric) : fixed100

For example:

`fixed100(1050.1012345)` returns 1050.10

Formats a number.
format_number(number : numeric, format : string) : string
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"

measure "ED Average Time to Admission" `format_timevalue(average(value("ED Time to Admit")), "HH:MM")` filter=`value("ED Time to Admit Encounter") > 0` converts the numeric value of the average into a string formatted as hours and minutes

Convert a numeric value to an integer. For more information, see Integer Data Type.
integer(number : numeric) : integer
This function is similar to trunc(number) except that the result is an integer. Note that use of trunc, ceil , floor, or round is recommended instead because these functions are more explicit about rounding.

For example,

`integer(2.3)` returns 2

`integer(2.7)` returns 2

`integer(-2.3)` returns -2

`integer(-2.7)` returns -2

`integer(round(parse_double("12.9")))` returns 13

Parse a string as a double.
parse_double(string : string) : double

For example:

`parse_double("1234.567")` returns 1234.567 as a floating point numeric value

`parse_double("1234")` returns 1234.0 as the value

Parse a string as a fixed100.
parse_fixed100(string : string) : fixed100

For example:

`parse_fixed100("1234.56")` returns 1234.56 as a fixed100 numeric value

`parse_fixed100("1234.567")` returns <error>

Parse a string as a double.
parse_float(string : string) : double

For example:

`parse_float("1234.56789")` returns 1234.56789 as a double numeric value

Parse a string as an integer.
parse_integer(string : string) : integer

For example:

`parse_integer("12")` returns the integer 12

`parse_integer("-12")` returns the integer -12

`parse_integer("12.9")` returns<error>

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 conversion function and a date. The parameters determine which variant applies. For more information, see Period Data Type.
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

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", today())` returns the quarter containing today's date

`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("Date"))` returns a fiscal month value

(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

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 calendar name for the specified period value.
period_calendar(period : period) : string

For example:

`period_calendar(period("Standard", "year-quarter", date("2015/12/12")))` returns standard

`period_calendar(period("standard", "year-month", "2017/02"))` returns "standard"

`period_calendar(value("YearMo"))` returns the name of the custom calendar used by the custom YearMo period

(Deprecated syntax) `period_calendar(value("year-quarter"))` returns gregorian quarter

TIP: Used with period() to generate a period with the same calendar as another period. This allows you to create new periods using the same calendar as another period, but changing the period type, without needing to fix the calendar name in the expression. For example:

period(period_calendar(value("YearMo")), "year-quarter", date("2017/02/02")) returns the year-quarter containing Feb 2, 2017, using the same calendar as YearMo

NOTE: In earlier versions of Spectre, when period_calendar() is inside a period() function call, Spectre uses the calendar used by the period inside the period_calendar call.

Return the period type for the named period.
period_type(period : period) : string
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.
Convert a value to a string. For more information, see String Data Type.
string(value : any) : string
The formats used are as follows: "#" for integer, "#.##" for fixed100, "#.#######" for double, "YYYY/MM/DD" for date, and the default format of the calendar for periods. For custom formatting, see format_date under Date and Time Functions and format_period.

For example:

`string(12345)` returns "12345"

`string(12345.6)` returns "12345.6"

`string(date("2015-07-04", "YYYY-MM-DD"))` returns "2015/07/04"

`string(period("gregorian month", "2015-07", "YYYY-MM"))` returns "2015/07"

NOTE:

  • Avoid using string() to manipulate date values—it is much more efficient to use date and period functions with a format tag.
  • Use of string() with a string column is superfluous and causes extra processing.
  • Do not use the string() function when there are alternatives. For example:
  • Do not test for a null with string(value("Date")) = ""; instead use is_null(value("Date")).
  • In general, do not test for null with `string(...) = ""` ; instead use `isnull(...)`.
  • Do not extract the year using parse_integer(substr(string(value("Date")),1,4)); instead use year(value("Date")).
  • Use string processing in cBase builds rather than in cBase queries.
Convert a datetime value into a time string, dropping the date portion.
time_string(datetime : datetime) : string

For example:

`time_string(datetime("2018/12/22 13:23:34")) creates the string "13:23:34"

See also: