RetroSearch Browse

Conversion functions with a TRY_ prefix are special versions of their respective conversion functions. These functions return a NULL value instead of raising an error when the conversion cannot be performed:

These functions only support string expressions (i.e. VARCHAR or CHAR data type) as input.

Important

These error-handling conversion functions are optimized for situations where conversion errors are relatively infrequent:

If there are no (or very few) errors, they should result in no visible performance impact.
If there are a large number of conversion failures, using these functions can result in significantly slower performance. Also, when using them with the VARIANT type, some operations might result in reduced performance.

Numeric formats in conversion functions¶

The functions TO_DECIMAL , TO_NUMBER , TO_NUMERIC, and TO_DOUBLE accept an optional parameter that specifies the format of the input string, if the input expression evaluates to a string. For more information about the values this parameter can have, see SQL format models.

Date and time formats in conversion functions¶

The following functions allow you to specify the expected date, time, or timestamp format to parse or produce a string:

You specify the format in an optional argument, using the following case-insensitive elements to describe the format:

Format element

Description

YYYY

Four-digit year.

YY

Two-digit year, controlled by the TWO_DIGIT_CENTURY_START session parameter. For example, when set to 1980, values of 79 and 80 are parsed as 2079 and 1980 respectively.

MM

Two-digit month (01 = January, and so on).

MON

Full or abbreviated month name.

MMMM

Full month name.

DD

Two-digit day of month (01 through 31).

DY

Abbreviated day of week.

HH24

Two digits for hour (00 through 23). You must not specify AM / PM.

HH12

Two digits for hour (01 through 12). You can specify AM / PM.

AM , PM

Ante meridiem (AM) / post meridiem (PM). Use this only with HH12 (not with HH24).

MI

Two digits for minute (00 through 59).

SS

Two digits for second (00 through 59).

FF[0-9]

Fractional seconds with precision 0 (seconds) to 9 (nanoseconds), e.g. FF, FF0, FF3, FF9. Specifying FF is equivalent to FF9 (nanoseconds).

TZH:TZM , TZHTZM , TZH

Time zone hour and minute, offset from UTC. Can be prefixed by +/- for sign.

UUUU

Four-digit year in ISO format, which are negative for BCE years.

Note

When a date-only format is used, the associated time is assumed to be midnight on that day.
Anything in the format between double quotes or other than the above elements is parsed/formatted without being interpreted.
For more details about valid ranges, number of digits, and best practices, see Additional information about using date, time, and timestamp formats.

Usage notes¶

Anything in the format between double quotes or other than the above elements is parsed/formatted without being interpreted.

Examples¶

Convert a string to a date using a specified input format of dd/mm/yyyy. The display format for dates in the output is determined by the DATE_OUTPUT_FORMAT session parameter (default YYYY-MM-DD).

SELECT TO_DATE('3/4/2024', 'dd/mm/yyyy');

Copy

+-----------------------------------+
| TO_DATE('3/4/2024', 'DD/MM/YYYY') |
|-----------------------------------|
| 2024-04-03                        |
+-----------------------------------+

Convert a date to a string, and specify a date output format of mon dd, yyyy.

SELECT TO_VARCHAR('2024-04-05'::DATE, 'mon dd, yyyy');

Copy

+------------------------------------------------+
| TO_VARCHAR('2024-04-05'::DATE, 'MON DD, YYYY') |
|------------------------------------------------|
| Apr 05, 2024                                   |
+------------------------------------------------+

Binary formats in conversion functions¶

TO_CHAR , TO_VARCHAR, and TO_BINARY accept an optional argument specifying the expected format to parse or produce a string.

The format can be one of the following strings (case-insensitive):

For more information about these formats, see Overview of supported binary formats.

For examples of using these formats, see the Examples section of Binary input and output.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4