AntDB-Oracle Compatibility Developer’s Manual P3–49
Published: 2023-09-12 05:30:55

#### Conversion Function

To convert different data types (including date/time, integer, floating point, numeric) to formatted characters and formatted strings to specific data types, AntDB's formatting functions provide a number of powerful tools. These functions have a common calling convention: the first argument is the formatted value, and the second argument is a string template that defines the format of the output or input.

| Name| Number of arguments| Function| For example:
|----------|----------|----------|----------
| CONVERT| 2~3| Convert strings from one character set to another, but AntDB and Oracle have different supported character set names, which are not compatible.| convert('text\_in\_utf8', 'UTF8', 'LATIN1')
| NUMTODSINTERVAL| 1| NUMTODSINTERVAL(x) converts the number x to an INTERVAL DAY TO SECOND type.| NUMTODSINTERVAL(100, 'day')
| NUMTOYMINTERVAL| 1| NUMTOYMINTERVAL(x) converts the number x to an INTERVAL YEAR TO MONTH type.| NUMTOYMINTERVAL(1,'year')
| TO\_CHAR (bfile \| blob)| 1~2| Convert BFILE or BLOB to char type| TO\_CHAR(media\_col, 873)
| TO\_CHAR (character)| 1| Convert the specified parameter to string| TO\_CHAR('01110')
| TO\_CHAR (datetime)| 1~2| TO\_CHAR({ datetime \| interval } \[, fmt ])  outputs the date in the specified format| to\_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’)
| TO\_CHAR (number)| 1~2| TO\_CHAR(n \[, fmt]) formats the number according to the specified format| to\_char(1210.73, ‘9999.9’)
| TO\_DATE| 1~2| TO\_DATE(c\[,fmt]) converts c to a date type, or if the fmt parameter is not empty, to the format specified in fmt| TO\_DATE('January 15, 1989, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.')
| TO\_DSINTERVAL| 1| Convert a string to a value of the INTERVAL DAY TO SECOND data type, which can be used to add or subtract a datetime value.| TO\_DSINTERVAL('100 00:00:00') TO\_DSINTERVAL('P100DT05H')
| TO\_MULTI\_BYTE| 1| As opposed to TO\_SINGLE\_BYTE| dump(TO\_MULTI\_BYTE( 'A'))
| TO\_NUMBER| 1~2| TO\_NUMBER(c\[,fmt]) converts a string to a numeric type in the format specified in fmt and returns it| TO\_NUMBER('100.00', '9G999D99') to\_number('$123,233,455,623.3400','$999,999,999,999.0000')
| TO\_SINGLE\_BYTE| 1| Convert a multi-byte character in x to the corresponding single-byte character. The return type is the same as the x type| TO\_SINGLE\_BYTE( CHR(15711393))
| TO\_TIMESTAMP| 1~n| TO\_TIMESTAMP(c1\[,fmt]) Converts the specified character to timestamp format in the specified format.| TO\_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
| TO\_TIMESTAMP\_TZ| 1~n| Convert string to TIMESTAMP WITH TIME ZONE| TO\_TIMESTAMP\_TZ('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM')
| TO\_YMINTERVAL| 1~n| Convert the string X to an interval year to month type| select TO\_YMINTERVAL('01-02') "14 months" from dual; select TO\_YMINTERVAL('P1Y2M') from dual;

Note:

The function convert is to convert a string from one character set to another character set, but the character sets supported by AntDB and the character set names supported by Oracle are different. So, although the functions of the functions are compatible, the character set names are not compatible.

In the output template string of the TO\_CHAR function, here a fixed pattern is recognized and replaced by formatted values, while any text that is not the standard pattern is simply copied verbatim. In a similar case, in an input template string (for functions other than TO\_CHAR), the template pattern identifies the part of the input string and the value to look for.

The following table shows the template patterns that can be used to format values in the functions TO\_CHAR and TO\_DATE.

| Mode| Description
|----------|----------
| HH| Hours of the day (01-12)
| HH12| Hours of the day (01-12)
| HH24| Hours of the day (00-23)
| MI| Minutes (00-59)
| SS| Seconds (00- 59)
| SSSSS| Number of seconds elapsed since midnight (0-86399)
| AM、A.M. 、PM 、P.M.| Noon Instruction (upper case)
| am 、a.m. 、pm 、p.m.| Noon Instruction (lower case)
| Y,YYY| Year (consisting of 4 to more numbers), separated by commas
| YEAR| Year
| SYEAR| Year (dates in B.C. can be preceded by a minus sign)
| YYYY| Year (consisting of 4 to more digits)
| SYYYY| Indicates the year (composed of 4 to more digits) (dates that indicate B.C. can be preceded by a minus sign)
| YYY| Indicates the last three digits of the year
| YY| Indicates the last two digits of the year
| Y| Indicates the last one digit of the year
| IYYY| Year in accordance with ISO representation (consisting of 4 or more digits)
| IYY| Last three digits of the year in accordance with ISO representation
| IY| Last two digits of the year in accordance with ISO representation
| I| Last one digit of the year in accordance with ISO representation
| BC or B.C. or AD or A.D.| Indicates the A.D. era (upper case)
| bc or b.c. or ad or a.d.| Indicates the A.D. era (lower case)
| MONTH| All capitalized month names
| Month| Mixed-case month names
| month| All lowercase month names
| MON| Abbreviation of the name of the month in uppercase (3 English characters, length will vary if localized language)
| Mon| Abbreviation of the name of the month in Mixed-case (3 English characters, length will vary if localized language)
| Mon| Abbreviation of the name of the month in lowercase (3 English characters, length will vary if localized language)
| MM| Represents the month number (01-12)
| DAY| The name of the day in upper case.
| Day| The name of the day in mixed-case
| Day| The name of the day in lower case
| DY| Abbreviation of the name of the day in uppercase (3 English characters, length will vary if localized language)
| Dy| Abbreviation of the name of the day in mixed-case (3 English characters, length will vary if localized language)
| Dy| Abbreviation of the name of the day in lowercase (3 English characters, length will vary if localized language)
| DDD| Days of the year (001-366)
| DD| Days of the month (01-31)
| D| Number of days in a week (1-7, Sundays are represented by 1)
| W| Number of weeks in the month (1-5) (the first week starts from the first day of the month)
| WW| Number of weeks in the year (1-53) (the first week starts from the first day of the year)
| IW| The number of weeks in the ISO year; the date of the first week in which the first Tuesday of the new year falls
| CC| Number of centuries (2 digits); 21st century starts from 2001-01-01
| SCC| The same number as the century, except that the BC date is prefixed by a minus sign.
| J| Julian date (from January 1, 4712 B.C.)
| Q| Quarter
| RM| Month in Roman numerals (I-XII; I for January) (upper case)
| Rm| Month in Roman numerals (I-XII; I for January) (lower case)
| RR| When the last two digits of the year are given, the first 2 digits of the year are determined. The result is obtained by using the current year and the given algorithm that represents the year with 2 digits. The first two digits of the year expressed with 2 digits will be the same as the first two digits of the current year. Except in the following cases. If the given year expressed with 2 digits is less than 50 and the last two digits of the current year are greater than or equal to 50, then the first two digits of the current year plus 1 will be equal to the first two digits of the given year. If a given year with 2 digits is greater than or equal to 50 and the last two digits of the current year are less than 50, then the first two digits of the current year minus 1 equals the first two digits of the given year.
| RRRR| Only affects the function TO\_DATE, allowing the specification of the year expressed in 2 or 4 digits. If a 2-digit year is specified, then the first two digits of the year as represented in RR format will be returned, and if a 4-digit year is used, then the 4-digit year will be returned

Certain modifiers can be applied to any template schema to be used to modify its behavior. For example, FMMonth is the Month pattern with FM modifiers. The following table shows the pattern modifiers for the date/time format.

| Modifier| Description| Example
|----------|----------|----------
| FM prefix| Fill mode (compression of added spaces and zeros)| FMMonth
| TH suffix| Convert ordinal suffixes to uppercase| DDTH
| th suffix| Convert ordinal suffixes to lowercase| DDth
| FX prefix| Fixed format global options (see usage notes)| FX Month DD Day
| SP suffix| Spelling mode| DDSP

*Notes on the use of the date/time format function.*

- FM compresses the leading zeros and trailing spaces that are used to make the output conform to a fixed-width pattern.

- If the FX option is not used, TO\_TIMESTAMP and TO\_DATE skip multiple spaces in the input string. Fx must be specified as the first member of the template. For example TO\_TIMESTAMP('2000 JUN', 'YYYY MON') is correct in this way, but TO\_TIMESTAMP('2000 JUN', 'FXYYYY MON') returns an error because TO\_TIMESTAMP just expects one space.

- Plain text is allowed in the TO\_CHAR function, and the output of the function is implemented as text.

- In converting a string from type timestamp to date, the CC field can be ignored if there is a YYYY,YYYY, or Y,YYYY field here. If a CC value with a YY or Y field is used, then the year is calculated as (CC-1)\*100+YY.

The following table shows the template patterns that can be used when formatting values.

| Mode| Description
|----------|----------
| 9| A value with a specified number of digits
| 0| Values starting with zero
| . (period)| Decimal point
| , (comma)| Separator for groups (in 1000's)
| $| Dollar sign
| PR| Negative sign in pointed brackets
| S| Fixed sign for numeric values (using local settings)
| L| Currency symbols (using local settings)
| D| Decimal point (using local settings)
| G| Group separator (using local settings)
| MI| Negative sign at the indicated position (if number \< 0)
| PL| Negative sign at the indicated position (if number> 0)
| SG| Positive/negative sign at the indicated position
| RN or rn| Roman numerals (input range is from 1 to 3999)
| V| The number of digits moved (see the notes below)

Some notes on the use of the following value formats.

- The number 9 produces the same value as 9s, and if no number is specified then spaces are output.

- TH does not convert values less than 0 and does not convert fractional values.

V effectively multiplies the input value by the nth power of 10, where n is the number of digits following V.TO\_CHAR. The use of V and decimal combinations is not supported here (for example, the form 99.9V99 is not allowed).

The following table shows some examples of using the TO\_CHAR and TO\_DATE functions.

| expression| result
|----------|----------
| TO\_CHAR(CURRENT\_TIMESTAMP, 'Day, DD HH12:MI:SS')| ‘ Wednesday, 30 11:17:21'
| TO\_CHAR(CURRENT\_TIMESTAMP,'FMDay, FMDD HH12:MI:SS')| ‘ Wednesday, 30 11:16:34'
| TO\_CHAR(-0.1, '99.99')| ‘ -.10'
| TO\_CHAR(-0.1, 'FM9.99')| ‘-.1'
| TO\_CHAR(0.1, '0.9')| ‘ 0.1'
| TO\_CHAR(12, '9990999.9')| ‘ 0012.0'
| TO\_CHAR(12, 'FM9990999.9')| ‘0012.'
| TO\_CHAR(485, '999')| ‘ 485'
| TO\_CHAR(-485, '999')| ‘-485'
| TO\_CHAR(1485, '9,999')| ‘ 1,485'
| TO\_CHAR(1485, '9G999')| ‘ 1,485'
| TO\_CHAR(148.5, '999.999')| ‘ 148.500'
| TO\_CHAR(148.5, 'FM999.999')| ‘148.5'
| TO\_CHAR(148.5, 'FM999.990')| ‘148.500'
| TO\_CHAR(148.5, '999D999')| ‘ 148.500'
| TO\_CHAR(3148.5, '9G999D999')| ‘ 3,148.500'
| TO\_CHAR(-485, '999S')| ‘485-'
| TO\_CHAR(-485, '999MI')| ‘485-'
| TO\_CHAR(485, '999MI')| ‘485 '
| TO\_CHAR (-485,'9SG99')| ‘4-85’
| TO\_CHAR (485,'9PL99')| ‘4+85’
| TO\_CHAR(485, 'FM999MI')| ‘485'
| TO\_CHAR(-485, '999PR')| ‘\<485>'
| TO\_CHAR(485, 'L999')| ‘ 485'
| TO\_CHAR(485, 'RN')| ‘ CDLXXXV'
| TO\_CHAR(485, 'FMRN')| ‘CDLXXXV'
| TO\_CHAR(5.2, 'FMRN')| ‘V'
| TO\_CHAR(12, '99V999')| ‘ 12000'
| TO\_CHAR(12.4, '99V999')| ‘ 12400'
| TO\_CHAR(12.45, '99V9')| ‘ 125'