# Data type formatting functions Using a data type formatting function, you can convert values from one data type to another. For each of these functions, the first argument is always the value to be formatted and the second argument contains the template for the new format. AWS Clean Rooms Spark SQL supports several data type formatting functions. **Topics** + [BASE64 function](base64.md) + [CAST function](CAST_function.md) + [DECODE function](DECODE.md) + [ENCODE function](ENCODE.md) + [HEX function](HEX.md) + [STR\$1TO\$1MAP function](STR_TO_MAP.md) + [TO\$1CHAR](TO_CHAR.md) + [TO\$1DATE function](TO_DATE_function.md) + [TO\$1NUMBER](TO_NUMBER.md) + [UNBASE64 function](unbase64.md) + [UNHEX function](UNHEX.md) + [Datetime format strings](FORMAT_strings.md) + [Numeric format strings](Numeric_formating.md) # BASE64 function The BASE64 function converts an expression to a base 64 string using [RFC2045 Base64 transfer encoding for MIME](https://datatracker.ietf.org/doc/html/rfc2045). ## Syntax ``` base64(expr) ``` ## Arguments *expr* A BINARY expression or a STRING which the function will interpret as BINARY. ## Return type `STRING` ## Example To convert the given string input into its Base64 encoded representation. use the following example. The result is the Base64 encoded representation of the input string 'Spark SQL', which is 'U3BhcmsgU1FM'. ``` SELECT base64('Spark SQL'); U3BhcmsgU1FM ``` # CAST function The CAST function converts one data type to another compatible data type. For instance, you can convert a string to a date, or a numeric type to a string. CAST performs a runtime conversion, which means that the conversion doesn't change a value's data type in a source table. It's changed only in the context of the query. Certain data types require an explicit conversion to other data types using the CAST function. Other data types can be converted implicitly, as part of another command, without using CAST. See [Type compatibility and conversion](s_Type_conversion.md). ## Syntax Use either of these two equivalent syntax forms to cast expressions from one data type to another. ``` CAST ( expression AS type ) ``` ## Arguments *expression* An expression that evaluates to one or more values, such as a column name or a literal. Converting null values returns nulls. The expression can't contain blank or empty strings. *type* One of the supported [Data types](s_Supported_data_types.md) , except for BINARY and BINARY VARYING data types. ## Return type CAST returns the data type specified by the *type* argument. **Note** AWS Clean Rooms returns an error if you try to perform a problematic conversion, such as a DECIMAL conversion that loses precision, like the following: ``` select 123.456::decimal(2,1); ``` or an INTEGER conversion that causes an overflow: ``` select 12345678::smallint; ``` ## Examples The following two queries are equivalent. They both cast a decimal value to an integer: ``` select cast(pricepaid as integer) from sales where salesid=100; pricepaid ----------- 162 (1 row) ``` ``` select pricepaid::integer from sales where salesid=100; pricepaid ----------- 162 (1 row) ``` The following produces a similar result. It doesn't require sample data to run: ``` select cast(162.00 as integer) as pricepaid; pricepaid ----------- 162 (1 row) ``` In this example, the values in a timestamp column are cast as dates, which results in removing the time from each result: ``` select cast(saletime as date), salesid from sales order by salesid limit 10; saletime | salesid -----------+--------- 2008-02-18 | 1 2008-06-06 | 2 2008-06-06 | 3 2008-06-09 | 4 2008-08-31 | 5 2008-07-16 | 6 2008-06-26 | 7 2008-07-10 | 8 2008-07-22 | 9 2008-08-06 | 10 (10 rows) ``` If you didn't use CAST as illustrated in the previous sample, the results would include the time: *2008-02-18 02:36:48*. The following query casts variable character data to a date. It doesn't require sample data to run. ``` select cast('2008-02-18 02:36:48' as date) as mysaletime; mysaletime -------------------- 2008-02-18 (1 row) ``` In this example, the values in a date column are cast as timestamps: ``` select cast(caldate as timestamp), dateid from date order by dateid limit 10; caldate | dateid --------------------+-------- 2008-01-01 00:00:00 | 1827 2008-01-02 00:00:00 | 1828 2008-01-03 00:00:00 | 1829 2008-01-04 00:00:00 | 1830 2008-01-05 00:00:00 | 1831 2008-01-06 00:00:00 | 1832 2008-01-07 00:00:00 | 1833 2008-01-08 00:00:00 | 1834 2008-01-09 00:00:00 | 1835 2008-01-10 00:00:00 | 1836 (10 rows) ``` In a case like the previous sample, you can gain additional control over output formatting by using [TO\$1CHAR](TO_CHAR.md). In this example, an integer is cast as a character string: ``` select cast(2008 as char(4)); bpchar -------- 2008 ``` In this example, a DECIMAL(6,3) value is cast as a DECIMAL(4,1) value: ``` select cast(109.652 as decimal(4,1)); numeric --------- 109.7 ``` This example shows a more complex expression. The PRICEPAID column (a DECIMAL(8,2) column) in the SALES table is converted to a DECIMAL(38,2) column and the values are multiplied by 100000000000000000000: ``` select salesid, pricepaid::decimal(38,2)*100000000000000000000 as value from sales where salesid<10 order by salesid; salesid | value ---------+---------------------------- 1 | 72800000000000000000000.00 2 | 7600000000000000000000.00 3 | 35000000000000000000000.00 4 | 17500000000000000000000.00 5 | 15400000000000000000000.00 6 | 39400000000000000000000.00 7 | 78800000000000000000000.00 8 | 19700000000000000000000.00 9 | 59100000000000000000000.00 (9 rows) ``` # DECODE function The DECODE function is the counterpart to the ENCODE function, which is used to convert a string to a binary format using a specific character encoding. The DECODE function takes the binary data and converts it back to a readable string format using the specified character encoding. This function is useful when you need to work with binary data stored in a database and need to present it in a human-readable format, or when you need to convert data between different character encodings. ## Syntax ``` decode(expr, charset) ``` ## Arguments *expr* A BINARY expression encoded in charset. *charset* A STRING expression. Supported character set encodings (case-insensitive): `'US-ASCII'`, `'ISO-8859-1'`, `'UTF-8'`, `'UTF-16BE'`, `'UTF-16LE'`, and `'UTF-16'`. ## Return type The DECODE function returns a STRING. ## Example The following example has a table called `messages` with a column called `message_text` that stores message data in a binary format using the UTF-8 character encoding. The DECODE function converts the binary data back to a readable string format. The output of this query is the readable text of the message stored in the messages table, with the ID `123`, converted from the binary format to a string using the `'utf-8'` encoding. ``` SELECT decode(message_text, 'utf-8') AS message FROM messages WHERE message_id = 123; ``` # ENCODE function The ENCODE function is used to convert a string to its binary representation using a specified character encoding. This function is useful when you need to work with binary data or when you need to convert between different character encodings. For example, you might use the ENCODE function when storing data in a database that requires binary storage, or when you need to transfer data between systems that use different character encodings. ## Syntax ``` encode(str, charset) ``` ## Arguments *str* A STRING expression to be encoded. *charset* A STRING expression specifying the encoding. Supported character set encodings (case-insensitive): `'US-ASCII'`, `'ISO-8859-1'`, `'UTF-8'`, `'UTF-16BE'`, `'UTF-16LE'`, and `'UTF-16'`. ## Return type The ENCODE function returns a BINARY. ## Example The following example converts the string `'abc'` to its binary representation using the `'utf-8'` encoding, which in this case results in the original string being returned. This is because the `'utf-8'` encoding is a variable-width character encoding that can represent the entire ASCII character set (which includes the letters `'a'`, `'b'`, and `'c'`) using a single byte per character. Therefore, the binary representation of `'abc'` using `'utf-8'` is the same as the original string. ``` SELECT encode('abc', 'utf-8'); abc ``` # HEX function The HEX function converts a numeric value (either an integer or a floating-point number) to its corresponding hexadecimal string representation. Hexadecimal is a numeral system that uses 16 distinct symbols (0-9 and A-F) to represent numeric values. It is commonly used in computer science and programming to represent binary data in a more compact and human-readable format. ## Syntax ``` hex(expr) ``` ## Arguments *expr* A BIGINT, BINARY, or STRING expression. ## Return type HEX returns a STRING. The function returns the hexadecimal representation of the argument. ## Example The following example takes the integer value 17 as input and applies the HEX() function to it. The output is `11`, which is the hexadecimal representation of the input value `17`. ``` SELECT hex(17); 11 ``` The following example converts the string `'Spark_SQL'` to its hexadecimal representation. The output is `537061726B2053514C`, which is the hexadecimal representation of the input string `'Spark_SQL'`. ``` SELECT hex('Spark_SQL'); 537061726B2053514C ``` In this example, the string 'Spark\$1SQL' is converted as follows: + 'S' -> 53 + 'p' -> 70 + 'a' -> 61 + 'r' -> 72 ' + k' -> 6B + '\$1' -> 20 + 'S' -> 53 + 'Q' -> 51 + 'L' -> 4C The concatenation of these hexadecimal values results in the final output "`537061726B2053514C"`. # STR\$1TO\$1MAP function The STR\$1TO\$1MAP function is a string-to-map conversion function. It converts a string representation of a map (or dictionary) into an actual map data structure. This function is useful when you need to work with map data structures in SQL, but the data is initially stored as a string. By converting the string representation to an actual map, you can then perform operations and manipulations on the map data. ## Syntax ``` str_to_map(text[, pairDelim[, keyValueDelim]]) ``` ## Arguments *text* A STRING expression that represents the map. *pairDelim* An optional STRING literal that specifies how to separate entries. It defaults to a comma (`','`). *keyValueDelim* An optional STRING literal that specifies how to separate each key-value pair. It defaults to a colon (`':'`). ## Return type The STR\$1TO\$1MAP function returns a MAP of STRING for both keys and values. Both *pairDelim* and *keyValueDelim* are treated as regular expressions. ## Example The following example takes the input string and the two delimiter arguments, and converts the string representation into an actual map data structure. In this specific example, the input string `'a:1,b:2,c:3'` represents a map with the following key-value pairs: `'a'` is the key, and `'1'` is the value. `'b'` is the key, and `'2'` is the value. `'c'` is the key, and `'3'` is the value. The `','` delimiter is used to separate the key-value pairs, and the `':'` delimiter is used to separate the key and value within each pair. The output of this query is: `{"a":"1","b":"2","c":"3"}`. This is the resulting map data structure, where the keys are `'a'`, `'b'`, and `'c'`, and the corresponding values are `'1'`, `'2'`, and `'3'`. ``` SELECT str_to_map('a:1,b:2,c:3', ',', ':'); {"a":"1","b":"2","c":"3"} ``` The following example demonstrates that the STR\$1TO\$1MAP function expects the input string to be in a specific format, with the key-value pairs delimited correctly. If the input string doesn't match the expected format, the function will still attempt to create a map, but the resulting values may not be as expected. ``` SELECT str_to_map('a'); {"a":null} ``` # TO\$1CHAR TO\$1CHAR converts a timestamp or numeric expression to a character-string data format. ## Syntax ``` TO_CHAR (timestamp_expression | numeric_expression , 'format') ``` ## Arguments *timestamp\$1expression* An expression that results in a TIMESTAMP or TIMESTAMPTZ type value or a value that can implicitly be coerced to a timestamp. *numeric\$1expression* An expression that results in a numeric data type value or a value that can implicitly be coerced to a numeric type. For more information, see [Numeric types](Numeric_types.md). TO\$1CHAR inserts a space to the left of the numeral string. TO\$1CHAR doesn't support 128-bit DECIMAL values. *format* The format for the new value. For valid formats, see [Datetime format strings](FORMAT_strings.md) and [Numeric format strings](Numeric_formating.md). ## Return type VARCHAR ## Examples The following example converts a timestamp to a value with the date and time in a format with the name of the month padded to nine characters, the name of the day of the week, and the day number of the month. ``` select to_char(timestamp '2009-12-31 23:15:59', 'MONTH-DY-DD-YYYY HH12:MIPM'); to_char ------------------------- DECEMBER -THU-31-2009 11:15PM ``` The following example converts a timestamp to a value with day number of the year. ``` select to_char(timestamp '2009-12-31 23:15:59', 'DDD'); to_char ------------------------- 365 ``` The following example converts a timestamp to an ISO day number of the week. ``` select to_char(timestamp '2022-05-16 23:15:59', 'ID'); to_char ------------------------- 1 ``` The following example extracts the month name from a date. ``` select to_char(date '2009-12-31', 'MONTH'); to_char ------------------------- DECEMBER ``` The following example converts each STARTTIME value in the EVENT table to a string that consists of hours, minutes, and seconds. ``` select to_char(starttime, 'HH12:MI:SS') from event where eventid between 1 and 5 order by eventid; to_char ---------- 02:30:00 08:00:00 02:30:00 02:30:00 07:00:00 (5 rows) ``` The following example converts an entire timestamp value into a different format. ``` select starttime, to_char(starttime, 'MON-DD-YYYY HH12:MIPM') from event where eventid=1; starttime | to_char ---------------------+--------------------- 2008-01-25 14:30:00 | JAN-25-2008 02:30PM (1 row) ``` The following example converts a timestamp literal to a character string. ``` select to_char(timestamp '2009-12-31 23:15:59','HH24:MI:SS'); to_char ---------- 23:15:59 (1 row) ``` The following example converts a number to a character string with the negative sign at the end. ``` select to_char(-125.8, '999D99S'); to_char --------- 125.80- (1 row) ``` The following example converts a number to a character string with the currency symbol. ``` select to_char(-125.88, '$S999D99'); to_char --------- $-125.88 (1 row) ``` The following example converts a number to a character string using angle brackets for negative numbers. ``` select to_char(-125.88, '$999D99PR'); to_char --------- $<125.88> (1 row) ``` The following example converts a number to a Roman numeral string. ``` select to_char(125, 'RN'); to_char --------- CXXV (1 row) ``` The following example displays the day of the week. ``` SELECT to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS'); to_char ----------------------- Wednesday, 31 09:34:26 ``` The following example displays the ordinal number suffix for a number. ``` SELECT to_char(482, '999th'); to_char ----------------------- 482nd ``` The following example subtracts the commission from the price paid in the sales table. The difference is then rounded up and converted to a roman numeral, shown in the `to_char` column: ``` select salesid, pricepaid, commission, (pricepaid - commission) as difference, to_char(pricepaid - commission, 'rn') from sales group by sales.pricepaid, sales.commission, salesid order by salesid limit 10; salesid | pricepaid | commission | difference | to_char ---------+-----------+------------+------------+----------------- 1 | 728.00 | 109.20 | 618.80 | dcxix 2 | 76.00 | 11.40 | 64.60 | lxv 3 | 350.00 | 52.50 | 297.50 | ccxcviii 4 | 175.00 | 26.25 | 148.75 | cxlix 5 | 154.00 | 23.10 | 130.90 | cxxxi 6 | 394.00 | 59.10 | 334.90 | cccxxxv 7 | 788.00 | 118.20 | 669.80 | dclxx 8 | 197.00 | 29.55 | 167.45 | clxvii 9 | 591.00 | 88.65 | 502.35 | dii 10 | 65.00 | 9.75 | 55.25 | lv (10 rows) ``` The following example adds the currency symbol to the difference values shown in the `to_char` column: ``` select salesid, pricepaid, commission, (pricepaid - commission) as difference, to_char(pricepaid - commission, 'l99999D99') from sales group by sales.pricepaid, sales.commission, salesid order by salesid limit 10; salesid | pricepaid | commission | difference | to_char --------+-----------+------------+------------+------------ 1 | 728.00 | 109.20 | 618.80 | $ 618.80 2 | 76.00 | 11.40 | 64.60 | $ 64.60 3 | 350.00 | 52.50 | 297.50 | $ 297.50 4 | 175.00 | 26.25 | 148.75 | $ 148.75 5 | 154.00 | 23.10 | 130.90 | $ 130.90 6 | 394.00 | 59.10 | 334.90 | $ 334.90 7 | 788.00 | 118.20 | 669.80 | $ 669.80 8 | 197.00 | 29.55 | 167.45 | $ 167.45 9 | 591.00 | 88.65 | 502.35 | $ 502.35 10 | 65.00 | 9.75 | 55.25 | $ 55.25 (10 rows) ``` The following example lists the century in which each sale was made. ``` select salesid, saletime, to_char(saletime, 'cc') from sales order by salesid limit 10; salesid | saletime | to_char ---------+---------------------+--------- 1 | 2008-02-18 02:36:48 | 21 2 | 2008-06-06 05:00:16 | 21 3 | 2008-06-06 08:26:17 | 21 4 | 2008-06-09 08:38:52 | 21 5 | 2008-08-31 09:17:02 | 21 6 | 2008-07-16 11:59:24 | 21 7 | 2008-06-26 12:56:06 | 21 8 | 2008-07-10 02:12:36 | 21 9 | 2008-07-22 02:23:17 | 21 10 | 2008-08-06 02:51:55 | 21 (10 rows) ``` The following example converts each STARTTIME value in the EVENT table to a string that consists of hours, minutes, seconds, and time zone. ``` select to_char(starttime, 'HH12:MI:SS TZ') from event where eventid between 1 and 5 order by eventid; to_char ---------- 02:30:00 UTC 08:00:00 UTC 02:30:00 UTC 02:30:00 UTC 07:00:00 UTC (5 rows) (10 rows) ``` The following example shows formatting for seconds, milliseconds, and microseconds. ``` select sysdate, to_char(sysdate, 'HH24:MI:SS') as seconds, to_char(sysdate, 'HH24:MI:SS.MS') as milliseconds, to_char(sysdate, 'HH24:MI:SS:US') as microseconds; timestamp | seconds | milliseconds | microseconds --------------------+----------+--------------+---------------- 2015-04-10 18:45:09 | 18:45:09 | 18:45:09.325 | 18:45:09:325143 ``` # TO\$1DATE function TO\$1DATE converts a date represented by a character string to a DATE data type. ## Syntax ``` TO_DATE (date_str) ``` ``` TO_DATE (date_str, format) ``` ## Arguments *date\$1str* A date string or a data type that can be cast into a date string. *format* A string literal that matches Spark's datetime patterns. For valid datetime patterns, see [Datetime Patterns for Formatting and Parsing](https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html). ## Return type TO\$1DATE returns a DATE, depending on the *format* value. If the conversion to *format* fails, then an error is returned. ## Examples The following SQL statement converts the date `02 Oct 2001` into a date data type. ``` select to_date('02 Oct 2001', 'dd MMM yyyy'); to_date ------------ 2001-10-02 (1 row) ``` The following SQL statement converts the string `20010631` to a date. ``` select to_date('20010631', 'yyyyMMdd'); ``` The following SQL statement converts the string `20010631` to a date: ``` to_date('20010631', 'YYYYMMDD', TRUE); ``` The result is a null value because there are only 30 days in June. ``` to_date ------------ NULL ``` # TO\$1NUMBER TO\$1NUMBER converts a string to a numeric (decimal) value. ## Syntax ``` to_number(string, format) ``` ## Arguments *string* String to be converted. The format must be a literal value. *format* The second argument is a format string that indicates how the character string should be parsed to create the numeric value. For example, the format `'99D999'` specifies that the string to be converted consists of five digits with the decimal point in the third position. For example, `to_number('12.345','99D999')` returns `12.345` as a numeric value. For a list of valid formats, see [Numeric format strings](Numeric_formating.md). ## Return type TO\$1NUMBER returns a DECIMAL number. If the conversion to *format* fails, then an error is returned. ## Examples The following example converts the string `12,454.8-` to a number: ``` select to_number('12,454.8-', '99G999D9S'); to_number ----------- -12454.8 ``` The following example converts the string `$ 12,454.88` to a number: ``` select to_number('$ 12,454.88', 'L 99G999D99'); to_number ----------- 12454.88 ``` The following example converts the string `$ 2,012,454.88` to a number: ``` select to_number('$ 2,012,454.88', 'L 9,999,999.99'); to_number ----------- 2012454.88 ``` # UNBASE64 function The UNBASE64 function converts an argument from a base 64 string to a binary. Base64 encoding is commonly used to represent binary data (such as images, files, or encrypted information) in a textual format that is safe for transmission over various communication channels (such as email, URL parameters, or database storage). The UNBASE64 function allows you to reverse this process and recover the original binary data. This type of functionality can be useful in scenarios where you need to work with data that has been encoded in Base64 format, such as when integrating with external systems or APIs that use Base64 as a data transfer mechanism. ## Syntax ``` unbase64(expr) ``` ## Arguments *expr* A STRING expression in a base64 format. ## Return type `BINARY` ## Example In the following example, the Base64-encoded string `'U3BhcmsgU1FM'` is converted back to the original string `'Spark SQL'`. ``` SELECT unbase64('U3BhcmsgU1FM'); Spark SQL ``` # UNHEX function The UNHEX function converts a hexadecimal string back to its original string representation. This function can be useful in scenarios where you need to work with data that has been stored or transmitted in a hexadecimal format, and you need to restore the original string representation for further processing or display. The UNHEX function is the counterpart to the [HEX function](HEX.md). ## Syntax ``` unhex(expr) ``` ## Arguments *expr* A STRING expression of hexadecimal characters. ## Return type UNHEX returns a BINARY. If the length of *expr* is odd, the first character is discarded and the result is padded with a null byte. If *expr* contains non hex characters the result is NULL. ## Example The following example converts a hexadecimal string back to its original string representation by using the UNHEX() and DECODE() functions together. The first part of the query, uses the UNHEX() function to convert the hexadecimal string '537061726B2053514C' to its binary representation. The second part of the query, uses the DECODE() function to convert the binary data obtained from the UNHEX() function back to a string, using the 'UTF-8' character encoding. The output of the query, is he original string 'Spark\$1SQL' that was converted to hexadecimal and then back to a string. ``` SELECT decode(unhex('537061726B2053514C'), 'UTF-8'); Spark SQL ``` # Datetime format strings You can use datetime patterns in the following common scenarios: + When working with CSV and JSON data sources to parse and format datetime content + When converting between string types and date or timestamp types using functions such as: + unix\$1timestamp + date\$1format + to\$1unix\$1timestamp + from\$1unixtime + to\$1date + to\$1timestamp + from\$1utc\$1timestamp + to\$1utc\$1timestamp Use the pattern letters in the following table for date and timestamp parsing and formatting. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/FORMAT_strings.html) The number of pattern letters determines the format type: Text Format + Use 1-3 letters for the abbreviated form (for example, "Mon" for Monday) + Use exactly 4 letters for the full form (for example, "Monday") + Don't use 5 or more letters - this will cause an error Number Format (n) + The value n represents the maximum number of letters allowed + For single letter patterns: + Output uses minimum digits without padding + For multiple letter patterns: + Output is padded with zeros to match the letter count width + When parsing, input must contain the exact number of digits Number/Text Format + For 3 or more letters, follow the Text Format rules + For fewer letters, follow the Number Format rules Fraction Format + Use 1-9 'S' characters (for example, SSSSSS) + For parsing: + Accept fractions between 1 and the number of S characters + For formatting: + Pad with zeros to match the number of S characters + Supports up to 6 digits for microsecond precision + Can parse nanoseconds but truncates extra digits Year Format + The letter count sets the minimum field width for padding + For two letters: + Prints the last two digits + Parses years between 2000-2099 + For less than four letters (except two): + Shows the sign only for negative years + Don't use 7 or more letters - this will cause an error Month Format + Use 'M' for standard form or 'L' for standalone form + Single 'M' or 'L': + Shows month numbers 1-12 without padding + 'MM' or 'LL': + Shows month numbers 01-12 with padding + 'MMM': + Shows abbreviated month name in standard form + Must be part of a full date pattern + 'LLL': + Shows abbreviated month name in standalone form + Use for month-only formatting + 'MMMM': + Shows full month name in standard form + Use for dates and timestamps + 'LLLL': + Shows full month name in standalone form + Use for month-only formatting Time Zone Formats + am-pm: Use 1 letter only + Zone ID (V): Use 2 letters only + Zone names (z): + 1-3 letters: Shows short name + 4 letters: Shows full name + Don't use 5 or more letters Offset Formats + X and x: + 1 letter: Shows hour (\$101) or hour-minute (\$10130) + 2 letters: Shows hour-minute without colon (\$10130) + 3 letters: Shows hour-minute with colon (\$101:30) + 4 letters: Shows hour-minute-second without colon (\$1013015) + 5 letters: Shows hour-minute-second with colon (\$101:30:15) + X uses 'Z' for zero offset + x uses '\$100', '\$10000', or '\$100:00' for zero offset + O: + 1 letter: Shows short form (GMT\$18) + 4 letters: Shows full form (GMT\$108:00) + Z: + 1-3 letters: Shows hour-minute without colon (\$10130) + 4 letters: Shows full localized form + 5 letters: Shows hour-minute-second with colon Optional Sections + Use square brackets [ ] to mark optional content + You can nest optional sections + All valid data appears in output + Input can omit entire optional sections **Note** The symbols 'E', 'F', 'q', and 'Q' work only for datetime formatting (like date\$1format). Don't use them for datetime parsing (like to\$1timestamp). # Numeric format strings The following numeric format strings apply to functions such as TO\$1NUMBER and TO\$1CHAR. + For examples of formatting strings as numbers, see [TO\$1NUMBER](TO_NUMBER.md). + For examples of formatting numbers as strings, see [TO\$1CHAR](TO_CHAR.md). [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_formating.html)