GL_DATE Variable

The GL_DATE environment variable specifies the end-user formats of values in DATE columns. This variable is supported in Informix database servers 7.2x, 8.x, 9.x, and 10.x. A GL_DATE format string can contain the following characters:

• One or more white-space characters
• An ordinary character (other than the % symbol or a white-space character)
• A formatting directive, which is composed of the % symbol followed by one or two conversion characters that specify the required replacement

Date formatting directives are defined in the following table.

Directive

Replaced By

%a

The abbreviated weekday name as defined in the locale

%A

The full weekday name as defined in the locale

%b

The abbreviated month name as defined in the locale

%B

The full month name as defined in the locale

%C

The century number (the year divided by 100 and truncated to an integer) as a decimal number (00 through 99)

%d

The day of the month as a decimal number (01 through 31)

A single digit is preceded by a zero (0).

%D

Same as the %m/%d/%y format

%e

The day of the month as a decimal number (1 through 31)

A single digit is preceded by a space.

%h

Same as the %b formatting directive

%iy

The year as a two-digit decade (00 through 99)

It is the Informix-specific formatting directive for %y.

%iY

The year as a four-digit decade (0000 through 9999)

It is the Informix-specific formatting directive for %Y.

%m

The month as a decimal number (01 through 12)

%n

A newline character

%t

The TAB character

%w

The weekday as a decimal number (0 through 6)

The 0 represents the locale equivalent of Sunday.

%x

A special date representation that the locale defines

%y

The year as a two-digit decade (00 through 99)

%Y

The year as a four-digit decade (0000 through 9999)

%%

% (to allow % in the format string)

White space or other nonalphanumeric characters must appear between any two formatting directives. If a GL_DATE variable format does not correspond to any of the valid formatting directives, errors can result when the database server attempts to format the date.

For example, for a U.S. English locale, you can format an internal DATE value for 09/29/1998 using the following format:

* Sep 29, 1998 this day is:(Tuesday), a fine day *

To create this format, set the GL_DATE environment variable to this value:

* %b %d, %Y this day is:(%A), a fine day *

To insert this date value into a database table that has a date column, you can perform the following types of inserts:

• Nonnative SQL, in which SQL statements are sent to the database server unchanged

  Enter the date value exactly as expected by the GL_DATE setting.

• Native SQL, in which escape syntax is converted to an Informix-specific format

  Enter the date value in the JDBC escape format yyyy-mm-dd; the value is converted to the GL_DATE format automatically.

The following example shows both types of inserts:

To retrieve the formatted GL_DATE DATE value from the database, call the getString() method of the ResultSet class.

To enter strings that represent dates into database table columns of char, varchar, or lvarchar type, you can also build date objects that represent the date string value. The date string value must be in GL_DATE format.

The following example shows both ways of selecting DATE values:

PreparedStatement pstmt = conn.prepareStatement("Select * from 
   tablename "
   + "where col2 like ?;");
pstmt.setString(1, "%Tue%");
ResultSet r = pstmt.executeQuery();
while(r.next())
   {
   String s = r.getString(1);
   java.sql.Date d = r.getDate(2);
   System.out.println("Select: column col1 (GL_DATE format) = <" 
      + s + ">");
   System.out.println("Select: column col2 (JDBC Escape format) = <" 
      + d + ">");
   }
r.close();
pstmt.close();