The GL_DATE variable

The GL_DATE environment variable specifies the end-user formats of values in DATE columns. A GL_DATE format string can contain the following characters:
  • One or more white space characters
  • An ordinary character (other than the percent symbol ( % ) or a white space character)
  • A formatting directive, which is composed of the percent 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 formatting directive that is specific to HCL OneDB™ for %y.

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

It is the formatting directive that is specific to HCL OneDB 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 - 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 - 99)
%Y The year as a four-digit decade (0000 - 9999)
%% % (to allow % in the format string)
Important: GL_DATE optional date format qualifiers for field specifications are not supported.

For example, by using %4m to display a month as a decimal number with a maximum field width of 4 is not supported.

The GL_DATE conversion modifier O, which indicates use of alternative digits for alternative date formats, is not supported.

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 a format that is specific to HCL OneDB

    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();