convert

Convert an expression to a data type or a character string

Syntax

convert(expression, convert_type)

convert(expression, {char | wchar}, width, convert_format)

convert_type:
          CHAR | TINYINT | SMALLINT | INTEGER | BIGINT | REAL | DATE | TIME | TIMESTAMP

Arguments

expr	The expression to be converted.
arg_type	Specifies the data type into which the expression is to be converted.
char | wchar	Specifies the character type of the result when using the second form of the convert function specified above.
width	The maximum width, in characters, of the result string.
fmt	The specification of the format of the result character string into which the numeric or date/time values will be converted. The individual elements of the format specifiers are described in the Numeric Format Specifier and Date/Time Format Specifier tables below.

Description

This system function converts an expression to a different type or string representation.  There are two forms of this function.

The first form of this function, shown above, converts an expression to the specified data type.  The second form converts an expression to a character string in the specified format. 

Numeric Format Specifier

The format specifier for numeric values is represented as shown in the box below.  The minimum specifier that must be used for a numeric format is "#".  If the display field width (width parameter) is too small to contain a numeric value, the convert function formats the value in exponential format (for example, 1.759263e08).

The elements for this specifier are explained in the following table.

Numeric Format Specifier Elements

Element	Description
[<< | >> | ><]	The justification specifier. You can specify left-justified text (<<), right-justified text (>>), or centered text (><). The default for numeric values is right-justified.
['text' | $]	A text character or string to use as a prefix for the result string. You must enclose the character or text with single quotation marks unless the prefix is one dollar sign.
[- | (]	The display specifier for negative values. You can show negative values with a minus sign or with parentheses around the value. If parentheses are used, positive values are shown with an ending space to ensure alignment of the decimal point.
[#,]#[.#[#]...]	The numeric format specifier. You can specify whether to show commas every third place before the decimal point. Also, you can specify how many digits (if any) to show after the decimal point.
[e | E]	Whether to use exponential format to show numeric values. If this option is omitted, exponential format is used only when the value is too large or small to be shown otherwise. You can specify display of an lowercase or uppercase exponent indicator.
['text' | $ | %]	A text character or string to use as a suffix for the result string. You must enclose the character or text with single quotation marks unless the suffix is one dollar or percent sign.

Formatting Date/Time Values

The format specifier for date/time values is given in the above syntax box. The date/time format specifier can contain any number of text items or special characters that are interspersed with the date or time codes. You can arrange these items in any order, but a time specifier must adhere to the ordering rules described in the syntax under "time_code". For the minute codes to be interpreted as minutes (and not months) they must follow the hour codes. You cannot specify the minutes of a time value without also specifying the hour. You can specify the hour by itself. Similarly, you cannot specify the seconds without having specified minutes and you cannot specify fractions of a second without specifying seconds. Thus, the order "hours, minutes, seconds, fractions" must be preserved.

Date and Time Format Specifier Elements

	General Formatting Elements
Element	Description
[<< | >> | ><]	The justification specifier. You can specify left-justified text (<<), right-justified text (>>), or centered text (><). The default for numeric values is left-justified.
'text' | spchar	A string or a special character (for example, "-", "/", or ".") to be copied into the result string. The special character is often useful in separating the entities within a date and time.
	Date-Specific Formatting Elements
Element	Description
m	Month number (1-12) without a leading zero.
mm	Month number with a leading zero.
mmm	Three-character month abbreviation (e.g., "Jan").
mon	Same as mmm.
mmmm	Fully spelled month name (e.g., "January").
month	Same as mmmm.
d	Day of month (1-31) without leading zero.
dd	Day of month with leading zero.
ddd	Three character day of week abbreviation (e.g., "Wed").
dddd	Fully spelled day of week (e.g., "Wednesday").
day	Same as dddd.
yy	Two-digit year AD with leading zero if year between 1950 and 2049; otherwise same as yyyy.
yyyy	Year AD up to four digits without leading zero.
	Time-Specific Formatting Elements
Element	Description
h	Hour of day (0-12 or 23) without leading zero.
hh	Hour of day with leading zero.
m	Minute of hour (0-59) without leading zero (only after h or hh).
mm	Minute of hour with leading zero (only after h or hh).
s	Second of minute (0-59) without leading zero (only after m or mm).
ss	Second of minute with leading zero (only after m or mm).
.f[f]...	Fraction of a second: four decimal place accuracy (only after s or ss).
a/p | am/pm | A/P | AM/PM	Hour of day is 0-12; AM or PM indicator will be output to result string (only after last time code element).

Example

The following examples show numeric format specifiers and their results.

Function                                         Result
convert(14773.1234, char, 10, "#.#")             "   14773.1"
convert(736620.3795, char, 12, "#,#.###")        "736,620.380"
convert(736620.3795, char, 12, "$#,#.##")        "$736,620.38"
convert(736620.3795, char, 12, "<<#.######e")    "7.366204e05"
convert(56.75, char, 8, "#.##%")                 "  56.75%"
convert(56.75, char, 8, "#.##' percent'")        "  56.75 percent"

The examples below show date/time format specifiers and corresponding results.  These examples show how Tuesday, October 23, 1951 at 4:42:27.1750 a.m.  can be returned.  The format specifier, rather than the entire function, is shown here in the left column.

Format Spec.                             Result
mmm dd, yyyy                             Oct 23, 1951
hh'hours' on ddd month dd, yyyy          04hours on Tue October 23, 1951
dd 'of' month 'of the year' yyyy         23 of October of the year 1951
dddd hh.mm.ss.ffff mm-dd-yyyy            Tuesday 04.42.27.1750 10-23-1951
'date:'yyyy.mm.dd 'at' hh:mm A/P         date:1951.10.23 at 04:42 AM