Conversion Functions
Spectre conversion functions convert one data type to another data type. These functions can be used in all Spectre file types.
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
date(string : string, format : string) : date
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
date(year: integer, month: integer, day: integer) : date
For example:
`date(2017, 11, 02)` returns 2017/11/02
date(datetime()) : date
For example:
`date(datetime("2016/12/22 13:23:34"))` creates the date representing December 22, 2016
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
datetime (date_time: string) : datetime
datetime (date: date, time: integer) : datetime
datetime (date_time: string, format: string) : datetime
The format is "MM/DD/YYYY hh:mm:ss". See the datetime section in Spectre Format Conventions for details.
`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
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
double(number : numeric) : double
For example:
`double(1)` returns 1.0.
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
fixed100(number : numeric) : fixed100
For example:
`fixed100(1050.1012345)` returns 1050.10
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
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
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
integer(number : numeric) : integer
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
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
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
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
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>
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
parse_float(string : string) : double
For example:
`parse_float("1234.56789")` returns 1234.56789 as a double numeric value
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
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>
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
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
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
- 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.
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
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.
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
period_type(period : period) : string
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
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"
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
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
string(value : any) : string
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.
![Closed](../Skins/Default/Stylesheets/Images/transparent.gif)
time_string(datetime : datetime) : string
For example:
`time_string(datetime("2018/12/22 13:23:34")) creates the string "13:23:34"
See also: