DBDATE Variable

Support for the DBDATE environment variable provides backward compatibility for client applications that are based on Informix database server versions prior to 7.2x, 8.x, or 9.x. You should use the GL_DATE environment variable for new applications.

The DBDATE environment variable specifies the end-user formats of values in DATE columns. End-user formats are used in the following ways:

• When you input DATE values, IBM Informix products use the DBDATE environment variable to interpret the input. For example, if you specify a literal DATE value in an INSERT statement, Informix database servers require this literal value to be compatible with the format specified by the DBDATE variable.
• When you display DATE values, IBM Informix products use the DBDATE environment variable to format the output.

With standard formats, you can specify the following attributes:

• The order of the month, day, and year in a date
• Whether the year is printed with two digits (Y2) or four digits (Y4)
• The separator between the month, day, and year

The format string can include the following characters:

• Hyphen ( - ), dot ( . ), and slash ( / ) are separator characters in a date format. A separator appears at the end of a format string (for example Y4MD-).
• A 0 indicates that no separator is displayed.
• D and M are characters that represent the day and the month.
• Y2 and Y4 are characters that represent the year and the number of digits in the year.

The following format strings are valid standard DBDATE formats:

• DMY2
• DMY4
• MDY4
• MDY2
• Y4MD
• Y4DM
• Y2MD
• Y2DM

The separator always goes at the end of the format string (for example, DMY2/). If no separator or an invalid character is specified, the slash ( / ) character is the default.

For the U.S. ASCII English locale, the default setting for DBDATE is Y4MD-, where Y4 represents a four-digit year, M represents the month, D represents the day, and hyphen ( - ) is the separator (for example, 1998-10-08).

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

• Nonnative SQL. SQL statements are sent to the database server unchanged. Enter the date value exactly as expected by the DBDATE setting.
• Native SQL. 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 DBDATE format automatically.

The following example shows both types of inserts (the DBDATE value is MDY2-):

stmt = conn.createStatement();
cmd = "create table tablename (col1 date, col2 varchar(20));";
rc = stmt.executeUpdate(cmd);..
.String[] dateVals = {"'08-10-98'", "{d '1998-08-11'}" };
String[] charVals = {"'08-10-98'", "'08-11-98'" };
int numRows = dateVals.length;
for (int i = 0; i < numRows; i++)
    {
    cmd = "insert into tablename values(" + dateVals[i] + ", " + 
        charVals[i] + ")";
    rc = stmt.executeUpdate(cmd);
    System.out.println("Insert: column col1 (date) = " + dateVals[i]);
    System.out.println("Insert: column col2 (varchar) = " + charVals[i]);
    } 

To retrieve the formatted DBDATE 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 build date objects that represent the date string value. The date string value needs to be in DBDATE format.

The following example shows both ways to select DATE values:

PreparedStatement pstmt = conn.prepareStatement("Select * from tablename "
    + "where col1 = ?;");
GregorianCalendar gc = new GregorianCalendar(1998, 7, 10);
java.sql.Date dateObj = new java.sql.Date(gc.getTime().getTime());
pstmt.setDate(1, dateObj);
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 (DBDATE format) = <" 
        + s + ">");
    System.out.println("Select: column col2 (JDBC Escape format) = <" 
        + d + ">");
    }
r.close();
pstmt.close();