Changes the data type of the incoming single value to the Number, Text, or Date data types.
Action |
Result |
Conversion of Text to Number |
Numerical value of the text taking into account the decimal separator. If the decimal separator is set correctly, any thousands separator is detected automatically. |
Conversion of Number to Text |
Text representation of the number in the internal format, or based on the language and the specified format. You can also specify a valid number of leading zeros. If nothing is specified here, the results are formatted in the numerical format. |
Conversion of Text to Date |
Date value of the text in the internal format, based on the specified format and, where applicable, the language. The date must be in the AD era. Date values before the common era are not supported. The time format must be specified. The time format is made up of sequences of characters, which stand for date fields, for example, year, month, day of the week, or minute, in the relevant language; separated by separators. In addition, the corresponding language must be specified. Non-editable text must be enclosed in quotation marks. When using the Q or q symbol for quarters, all other symbols except Y and y for years are ignored. Only the order of Q/q and Y/y is relevant. |
Conversion of Date to Text |
Text representation of the date in the internal format, or based on the language and the specified format Non-editable text must be enclosed in quotation marks. The format and language specifications are optional. If no format is specified, the data is output in the internal date format. If no language is specified, English (EN) is applied as the default language. |
Conversion of Number to Date |
Date value corresponding to the value of the number as milliseconds since 01/01/1970 |
Conversion of Date to Number |
Number of milliseconds since 01/01/1970 |
Internal number format
If the user is logged in in English, the number format is Anglo-Saxon style with a period as the decimal separator and at least one decimal place, but without grouping characters.
Internal date format
yyyy-Q for specifying to the nearest quarter, otherwise yyyy-MM-ddThh:mm:ss. The number of digits corresponds to the accuracy of the date, and the remaining digits are omitted. This is the transfer format.
Quarterly specifications
These are indicated by a Q within the section of the format that is not in single quotation marks.
Prerequisites for conversion of text into quarterly date values:
It is expected that a source value containing a quarterly date consists of just two sequences of figures indicating the year and the quarter. Any non-numerical characters can occur before, after and between them, for example, Quarter 04/2009.
The pattern uses Y or y as the symbol for the year and Q or q for the quarter, for example, quarter Q/y or Q Y.
Procedure:
The (first) two sequences of digits are determined from the source value.
The section of the format that is not enclosed between single quotation marks is used to determine whether q / Q or y / Y appears first.
If q or Q appears first, the first sequence of digits is interpreted as the quarter and the second as the year, otherwise the reverse.
Parameters
The following parameters are available.
Parameter |
Description |
---|---|
Single value |
Source: Single-value operator Data type: Date, Number, or Text Specification: Mandatory |
New type |
New single-value data type Default value: Text Specification: Mandatory |
Format (Date type) |
Time format for conversion from Date type to Text type and vice versa. The following formatting symbols are available when converting date into text: Year: y or Y Quarter: Q Month: M Calendar week: w Day of the week: E or e Day of the month: d Day of the year: D Hour: H or h Minute: m Second: s AM/PM: a Time zone: z (for example, GMT) RFC time zone: Z (for example, -0900) Era: G (must always be AD) Default value: MM/dd/yyyy |
Format (Date type) |
The following formatting symbols are available when converting text to date: Year: y Quarter: Q Calendar week: w Week of the month: W Day of the week: E Day of the month: d Day of the year: D Hour (0-23): H Hour (1-24): k Hour AM/PM (0-11): K Hour AM/PM (1-12): h Minute: m Second: s AM/PM: a Time zone: z RFC time zone: Z Era: G Default value: yyyy-MM-dd'T'HH:mm:ss; Permitted separators in both cases: Dash/minus (-), underscore (_), slash (/), period (.), colon (:), comma (,), tab character, and space. Specification: Mandatory |
Language |
Language if the target format is of the Date type. Available languages: de and en. Specification: Mandatory when using names of months and names of days of the week |
Decimal separator |
Separator for the decimal places, if the target format is of the Number type. Default value: Comma (,) Specification: Mandatory |
Format (Number source format) |
Number format for the conversion of the Number type to the Text type. You can select predefined formats or set your own format manually. With manual entry, the numbers before the decimal separator must have four digits ascending and then descending, for example, 1,234.321. After this, you can add text (such as the unit "hours" or km/h). Default value: 1234 Permitted separators: Thousands separator in German: period (.) Thousands separator in English: comma (,) Decimal separator in German: comma (,) Decimal separator in English: period (.) |
Leading zeros |
Number of leading zeros. The maximum number of leading zeros is the number of digits before the decimal separator. Example: Format: 1,234.12 and leading zeros: 5 Number -> Text 10,245 -> 00010,25 12000,4 -> 12000,4 89,7 -> 00089,70 |
Specify type |
Automatically specifies the data type of the source values. If the content of a column does not correspond to its assigned data type, a row is created in the operator which specifies the data type determined for this column Vice-versa, settings (lines) are removed from the operator, which would reset the data type of a column already typified correctly. |
Encoding (Text source format) |
Specifies the encoding of special characters, for the conversion of the Text type to the Text type, for example, "/", "&", "?". Apply UTF-8 encoding: encodes the entire text, using UTF-8 codes Decode UTF-8: decodes the entire text, using UTF-8 codes Apply URL encoding: keeps the URL specific characters For UTF-8 encoding/decoding the special characters must be masked in a valid URL. Only select this option if you are sure that all sections that make up the URL are already masked. |
The characters in the time format can be combined in any order and repeated any number of times.
Exceptions:
For a month, the number of characters must be >= 3 (MMM or MMMM) if the month is specified in text format (JAN, FEB, etc.) and <3 if it is specified as a figure. In this case, a language must also be specified so that the name of the month can be transformed correctly.
For a year format such as 2009, y can be specified any number of times, that is, yy and yyyy return 2009.
For a year format such as 09, however, yyyy returns the year 9 and yy the year 2009.
When formatting date values as days of the week for a date to text conversion, an e/E number < 4 returns the day abbreviations (MON, TUE, etc.), while e/E = or > 4 returns the full name of the day.
Only the month (M), minute (m), time zone (z), RFC time zone (Z) and calendar week (w) are case-sensitive.
When converting text to date, if the Q or q symbol is used for quarters all other symbols except Y and y are ignored. Only the order of Q/q and Y/y is decisive then.
For the reverse conversion from date to text, the Q/q can be combined with any other symbols, but may only occur once (not QQ/yy)
Quarter entries are currently only possible in the form YYYY-Q. These strings can only contain the year, separator, and quarter.
All other strings must be enclosed in single quotation marks ('). Spaces can be inside or outside, for example, 'On' dd.MM.yy 'at' hh:mm, or 'On 'dd.MM.yy' at 'hh:mm' '.
The space pattern in the source and target format must match, for example, "2 .3 .09" -> "d .M .y" but not "2. 3. 09" -> "d .M .y".
Examples
Source format: "22.3.2009"
Time format: "d.M.y" or "DDDD.MM.YYYY",
but not "DD.MMM.YYYY "
Source format:"03/22/09 30:24 PM"
Time format: "MM/DD/YY hh:mm a" or "M/d/y HH:mm A"
but not "M/d/y HH:MM A" or "m/d/y HH:mm A"
Source format: "Time: 2009-FEBRUARY-01T22:33:44"
Time format: "Time: 'y-MMM-d'T'h:m:s" or "'Time:' y-MMMMM-d'T'h:m:s",
but not "'Time: 'y-MM-d'T'h:m:s"
Source format: "3. quarter 2009"
Time format: "QY" or "Q'. quarter' y or "QQ/yyyy";
but not "YQ"