Date-time or interval text representation

The text representation of a date-time or interval value is a quoted string that contains a series of digits and symbols.

The DataBlade® API supports a text representation for date-time or interval values as quoted strings with the formats that the following table shows.

Table 1. Text representation for date-time or interval values
SQL data type	Text representation
DATETIME	Date-time string: The date-time string must match the qualifier of the DATETIME column. The default format of the date-time string for the largest DATETIME column is: `"yyyy-mm-dd HH:MM:SS.FFFF"`
INTERVAL	Interval string: The interval string must match the qualifiers of the INTERVAL column. INTERVAL columns have two classes. The default format of an interval string for the largest year-month interval follows: `"yyyy-mm"` The default format of an interval string for the largest day-time interval follows: `"dd HH:SS.FFFF"`

The text representations in the preceding table use the following abbreviations:

yyyy: The 4-digit year (for a DATETIME) or the number of years (for an INTERVAL).
mm: The 2-digit month (for a DATETIME) or the number of months (for an INTERVAL).
dd: The 2-digit day of the month (for a DATETIME) or the number of days (for an INTERVAL).
HH: The 2-digit hour (for a DATETIME) or the number of hours (for an INTERVAL).
MM: The 2-digit minute (for a DATETIME) or the number of minutes (for an INTERVAL).
SS: The 2-digit second (for a DATETIME) or the number of seconds (for an INTERVAL).
FFFF: A fraction of a second (for a DATETIME or for an INTERVAL). The precision of fractions can range from 1 to 5 digits.

The text representation of a DATETIME value is often called a date-time string. For example, the following date-time string contains the value for 2 p.m. on July 12, 1999, with a qualifier of YEAR TO SECOND:

"1999-07-12 14:00:00"

Usually, a date-time string must match the qualifier of the DATETIME binary representation with which the string is associated.

The following interval string indicates a passage of three years and three months: "03-06"

A locale defines the end-user format of a DATE, DATETIME, or INTERVAL value. The end-user format is the format in which data appears in a client application when the data is a literal string or character variable. The preceding strings are the end-user formats for the default locale, US English. A nondefault locale can define date or time end-user formats that are specific to a country or culture outside the US.

You can also customize the end-user format of DATETIME values with the GL_DATETIME and DBTIME environment variables. In nondefault locales, you must also enable the USE_DTENV environment variable before GL_DATETIME or DBTIME formats for DATETIME values can be correctly applied in operations that load, unload, insert, or update DATETIME values.

You can similarly customize the end-user format of DATE values by setting the DBDATE, GL_DATE, and DBTIME environment variables. End-user formats have no effect on the storage formats that the database server uses for DATE or DATETIME data types. For more information, see the Informix® GLS User's Guide.