• Contents
  • Index

Altair® Monarch®

 

Notes on Conversions

The following notes describe how conversions are performed in Monarch Data Prep Studio.

Converting text to dates

Monarch Data Prep Studio looks for text forms having 3 parts separated by some separator (e.g., 4/15/2015).

Valid separators include:

  • period (.)

  • forward slash (/)

  • dash (-)

  • space ( )

Whenever the text form has an ambiguous pattern (e.g., 3/8/2015, as this may be interpreted as mm/dd/yyyy or dd/mm/yyyy), the conversion is done using the date pattern specified in the column change operation. Currently, this date pattern always defaults to the Windows Short Date pattern defined in the system.

Converting dates to text

Date/time values are converted to text using the format “yyyy-MM-dd HH:mm:ss.”

Thus, an ISO8601-formatted date and time is always produced.

Converting text to number of seconds

Time formats are primarily of the form “HH:MM:SS.” Monarch Data Prep Studio is fairly flexible in looking for different time formats, including formats that include the following:

  •  AM/PM indicators

  • military time formats

  • separators other than “:.”

Converting numbers to date/time

 Monarch Data Prep Studio converts numbers to dates by considering the numeric value as the number of days (and fractions of days) since a specific "zero" date/time.

Monarch Data Prep Studio uses 1900-01-01 00:00:00 as its "zero" date/time. Thus:

  • a numeric value of 0.0 corresponds to 1900-01-01 00:00:00

  • 0.25 corresponds to 1900-01-01 06:00:00

  • 0.5 corresponds to 1900-01-01 12:00:00

  • 1.0 corresponds to 1900-01-01 00:00:00

  • Negative numeric values correspond to date/times before the zero date/time.

Monarch Data Prep Studio makes a special exception to the normal conversion rule when the numeric value falls in the range 19000101 to 99991231. This value would ordinarily be interpreted as a HUGE number of days past the zero date/time and would produce a null value for the conversion. However, Monarch Data Prep Studio converts 19000101 to a date/time of 1900-01-01. Similarly, 20150715 is converted to 2015-07-15.

In other words, Monarch Data Prep Studio considers values in this range to be numeric representations of ISO8601 dates.

Converting numbers to text

Numeric values are converted to text using the SQL General format.

The number is formatted without thousands separators and with a decimal separator of a period (.).

Converting dates to numbers

The conversion of date/time values to numbers is the inverse of the conversion of numbers to date/times.

Specifically, the number produced is the difference in days (and a fraction thereof) between the given date and the "zero" date/time (1900-01-01 00:00:00).

For example, a date of 1995-10-13 converts to 34983.

Converting text to numbers

The following general rules umbers may have embedded thousands separators (e.g., “1,234.56” converts to 1234.56).

  • The decimal separator is specified in the column change operation. Currently, this decimal separator always defaults to the Decimal Symbol specified in the Windows regional settings on the system in question.

  • The thousands separator switches automatically with the decimal separator. If the decimal separator is period (.), the thousands separator is a comma (,), and vice versa.

  • A negative sign (-) may appear either before or after the number (e.g., “-1,234.56” and “1,234.56-” both convert to -1234.56). Numbers surrounded by parentheses are also considered to be negative (e.g., “(1,234.56)” converts to -1234.56).

  • A leading currency symbol is accepted (e.g., “$27.15” converts to 27.15). Accepted currency symbols are dollar sign ($), British pound (£), yen (¥), and euro (€).

  • The numeric parser now recognizes forms like "6.022E+23" or "6.626E-34". The "E" may be in either uppercase or lowercase, and the plus sign may be omitted, thus "6.022e23" is the same as "6.022E+23".