TO_VARCHAR

​

Syntax

​

Default (scalar)

to_varchar(value)

• Use ordinary STRING values, TIMESTAMP with microsecond precision, and DECIMAL scale from 0 through 38. Formatting timestamps with negative fractional seconds before the Unix epoch is outside these forms.

​

Formatted (scalar)

to_varchar(value, format)

• Microsecond TIMESTAMP and DECIMAL scale 0..38; ordinary STRING formats may be constants or per-row columns.
• Floating-point values must be finite, have magnitude below the unsigned 64-bit limit, and use at most 18 fractional digit positions. DECIMAL formats use at most 38 fractional positions. Timestamps with negative fractional seconds before the Unix epoch are outside these forms.

​

Arguments

​

Default

​

Formatted

​

Returns

• Type: STRING
• NULL behavior: Typed NULL value returns NULL for the documented input types. A NULL format returns NULL. A malformed format, unsupported type/format pairing or invalid UTF-8 binary conversion raises an error.

​

Properties

​

Units

• Input unit: Type-specific value and optional format
• Output unit: STRING
• DATE default format is yyyy-MM-dd; TIMESTAMP default format is yyyy-MM-dd HH:mm:ss.
• Numeric 0 positions pad with zeros and 9 positions pad with spaces; decimal D/., grouping G/,, currency $, and sign S/MI/PR tokens are supported.
• Integer and DECIMAL numeric formatting use integer arithmetic. DECIMAL rounding increments the magnitude at a discarded digit of 5 or more; floating formatting rounds the absolute scaled DOUBLE, then restores the sign.
• Too many integral or fractional digits for a numeric pattern replace digit positions with # while retaining separators, currency and the applicable sign layout.

​

Usage notes

• The one-argument STRING form passes its value through; one-argument numeric values use plain conversion.
• Formatting uses the timestamp’s stored UTC clock time and does not adjust it to its named time zone.
• The format may be a per-row ordinary STRING column; NULL values or NULL formats preserve NULL rows.
• BINARY hex uses uppercase hexadecimal; base64 and utf-8 are also accepted, with invalid UTF-8 producing an error.
• Deterministic: A fixed value and format produce the same text.

​

Examples

​

Formatted example 1

SELECT to_varchar(DATE '2024-03-15','yyyy-MM-dd') AS result;

​

Formatted example 2

SELECT to_varchar(X'537061726B','hex') AS result;

​

Limitations

• STRING input with an explicit format and BINARY input without a format are not supported.
• Use the types listed for each form. Narrower integer types, decimals beyond 128-bit storage, timestamps with other precision, and alternate STRING representations are outside these forms.
• DECIMAL values with negative scale, NaN, infinity, and floating-point values beyond the stated range are outside these formatting forms.
• Avoid timestamps with negative fractional seconds before 1970-01-01 00:00:00 UTC; they can be formatted incorrectly.