The end-user format is the format in which a data value is displayed or entered in a client application as a literal string or a character variable.
An end-user format is useful for a data type whose format in the database is different from the format to which users are accustomed. The database server stores data for DATE, DATETIME, MONEY, and numeric data types in compact internal formats within the database.
For example, the database server stores a DATE value as an integer number of days since December 31, 1899, so the date 03/19/96 is 35142. This internal format is not intuitive.
IBM Informix products support end-user formats so that a client application can use this more intuitive form instead of the internal format. Literal strings or character variables can appear in SQL statements as column values or as arguments of SQL API library functions.
An IBM Informix product uses an end-user format when it encounters a string (a literal string or the value in a character variable) in these contexts:
For example, suppose that DB–Access has the default (U.S. English) as its client locale. The literal date in the following INSERT statement uses the end-user format for dates that the default locale defines:
INSERT INTO mytab ( date1 ) VALUES ( '03/19/96' )
When it receives the data from the client application, the database server uses the end-user format to interpret this literal date so that it can convert it to the corresponding internal format (35142).
For example, suppose that an ESQL/C client application has a French locale as its client locale, and this locale defines a date end-user format that formats dates as dd/mm/yy. The following rdatestr() function uses the end-user format for dates to obtain the value in the datestr character variable:
err = rdatestr(jdate, datestr);
The rdatestr() function uses the end-user format to determine how to format the internal format (35142) as a date string before it puts the value in the datestr variable. For more information about the effect of the GLS feature on SQL API library functions, see Using Enhanced ESQL/C Library Functions.
A GLS locale defines end-user formats for the following types of data:
You can specify number, currency, date, and time values in an end-user format that is specific to a given country or culture.
The following table lists the values that define the end-user format for each data type that uses end-user formats. For information about the environment variables, see GLS Environment Variables. For information about the locale categories, see Appendix A. Managing GLS Files.
When an IBM Informix product reads a string that contains numeric or monetary data, it uses the end-user format to determine how to convert this string to the internal value for the database column. When an IBM Informix product prints a string that contains numeric or monetary data, it uses the end-user format to determine how to format the internal value for the database column as a string.
End-user formats for numbers and currency specify these elements:
For example, this might specify that numbers always omit the separator after the millions position, which produces the following output: 1234,345.
In addition to this notation, monetary data also uses a currency symbol to identify the currency unit. This can appear at the front ($100) or back (100FF) of the monetary value. In this manual, the combination of currency symbol, decimal separator, and thousands separator is called currency notation.
When an IBM Informix product reads a string that contains time data, it uses the end-user format to determine how to convert this string to the internal integer value for a DATETIME column. When an IBM Informix product prints a string that contains time data, it uses the time end-user format to determine how to format the internal integer value for a DATETIME column as a string. In the same way, IBM Informix products use the date end-user format to read and print strings for the internal values of the date data types.
The end-user formats for date and time can include the names and abbreviations for days of the week and months of the year, and the commonly used representations for dates, time (12-hour and 24-hour), and DATETIME values.
End-user formats can include names of eras (as in the Japanese Imperial date system) and non-Gregorian calendars (such as the Arabic lunar calendar).
For example, the Taiwan culture uses the Ming Guo year format in addition to the Gregorian calendar year. For dates before 1912, Ming Guo years are negative. The Ming Guo year 0000 is undefined; any attempt to use it generates an error. The following table shows some era-based dates.
| Gregorian Year | Ming Guo Year | Remarks |
|---|---|---|
| 1993 | 82 | 1993 – 1911 = 82 |
| 1912 | 01 | 1912 – 1911 = 01 |
| 1911 | –01 | 1911 – 1912 = –01 |
| 1910 | –02 | 1910 – 1912 = –02 |
| 1900 | –12 | 1900 – 1912 = –12 |
Japanese Imperial-era dates are tied to the reign of the Japanese emperors. The following table shows Julian and Japanese era dates. It shows the Japanese era format in full, with abstract multibyte characters for the Japanese characters, and in an abbreviated form that uses romanized characters (gengo). The abbreviated form of the era uses the first letter of the English name for the Japanese era. For example, H represents the Heisei era.
| Gregorian Date | Abstract Japanese Era (in full) | Japanese Era (gengo) |
|---|---|---|
| 1868/09/08 | A1A2B1B201/09/08 | M01/09/08 |
| 1912/07/30 | A1A2B1B245/07/30 | M45/07/30 |
| 1912/07/31 | A1A2B1B201/07/31 | T01/07/31 |
| 1926/12/25 | A1A2B1B215/12/25 | T15/12/25 |
| 1926/12/26 | A1A2B1B201/12/26 | S01/12/26 |
| 1989/01/07 | A1A2B1B264/01/07 | S64/01/07 |
| 1989/01/08 | A1A2B1B201/01/08 | H01/01/08 |
| 1995/01/01 | A1A2B1B207/01/01 | H07/01/01 |
Here A1A2 and B1B2 represent multibyte Japanese characters. For more information, see Customizing Date and Time End-User Formats.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]