DBTIME environment variable

The DBTIME environment variable specifies a formatting mask for the display and data-entry format of DATETIME values.

TheDBTIME environment variable is useful in contexts where the DATETIME data values to be formatted by DBTIME have the same precision as the specified DBTIME setting. You might encounter unexpected or invalid display formats for DATETIME values that are declared with a different DATETIME qualifier.

literal: is a literal white space or any printable character.
min: is a literal integer, setting the minimum number of characters in the substring for the value that special specifies.
precision: is the number of digits for the value of any time unit, or the maximum number of characters in the name of a month.
special: is one of the placeholder characters that are listed following.

These terms and symbols are described in the pages that follow.

This quoted string can include literal characters and placeholders for the values of individual time units and other elements of a DATETIME value. DBTIME takes effect only when you call certain DATETIME routines. (For details, see the HCL OneDB™ ESQL/C Programmer's Manual.) If DBTIME is not set, the behavior of these routines is undefined, and "YYYY-MM-DD hh:mm:ss.fffff" is the default display and input format for DATETIME YEAR TO FRACTION(5) literal values in the default locale.

The percentage ( % ) symbol gives special significance to the special placeholder symbol that follows. Without a preceding % symbol, any character within the formatting mask is interpreted as a literal character, even if it is the same character as one of the placeholder characters in the following list. Note also that the special placeholder symbols are case sensitive.

The following characters within a DBTIME format string are placeholders for time units (or for other features) within a DATETIME value.

%b: is replaced by the abbreviated month name.
%B: is replaced by the full month name.
%d: is replaced by the day of the month as a decimal number [01,31].
%Fn: is replaced by a fraction of a second with a scale that the integer n specifies. The default value of n is 2; the range of n is 0 < n < 5.
%H: is replaced by the hour (24-hour clock).
%I: is replaced by the hour (12-hour clock).
%M: is replaced by the minute as a decimal number [00,59].
%m: is replaced by the month as a decimal number [01,12].
%p: is replaced by A.M. or P.M. (or the equivalent in the locale file).
%S: is replaced by the second as a decimal number [00,59].
%y: is replaced by the year as a four-digit decimal number.
%Y: is replaced by the year as a four-digit decimal number. User must enter a four-digit value.
%%: is replaced by % (to allow a literal % character in the format string).

For example, consider this display format for DATETIME YEAR TO SECOND:

Mar 21, 2013 at 16 h 30 m 28 s

If the user enters a two-digit year value, this value is expanded to 4 digits according to the DBCENTURY environment variable setting. If DBCENTURY is not set, then the string 19 is used by default for the first two digits.

Set DBTIME as the following command line (for the C shell) shows:

setenv DBTIME '%b %d, %Y at %H h %M m %S s'

The default DBTIME produces the following ANSI SQL string format:

2001-03-21 16:30:28

You can set the default DBTIME as the following example shows:

setenv DBTIME '%Y-%m-%d %H:%M:%S'

An optional field width and precision specification (w.p) can immediately follow the percent (%) character. It is interpreted as follows:

w: Specifies the minimum field width. The value is right-justified with blank spaces on the left.
-w: Specifies the minimum field width. The value is left-justified with blank spaces on the right.
0w: Specifies the minimum field width. The value is right-justified and padded with zeros on the left.
p: Specifies the precision of d, H, I, m, M, S, y, and Y time unit values, or the maximum number of characters in b and B month names.

The following limitations apply to field-width and precision specifications:

If the data value supplies fewer digits than precision specifies, the value is padded with leading zeros.
If a data value supplies more characters than precision specifies, excess characters are truncated from the right.
If no field width or precision is specified for d, H, I, m, M, S, or y placeholders, 0.2 is the default, or 0.4 for the Y placeholder.
A precision specification is significant only when converting a DATETIME value to an ASCII string, but not vice versa.

The F placeholder does not support this field-width and precision syntax.

Important: Any separator character between the %S and %F directives for DATETIME user formats must be explicitly defined. Specifying %S%F concatenates the digits that represent the integer and fractional parts of the seconds value.

Like DBDATE, GL_DATE, or GL_DATETIME, or USE_DTENV, the DBTIME setting controls only the character-string representation of data values. It cannot change the internal storage format of the DATETIME column. (For additional information about formatting DATE values, see the discussion of DBDATE in the topic DBDATE environment variable.)

DBTIME formats in nondefault locales

If you specify a locale other than U.S. English, the locale defines the culture-specific display formats for DATETIME values. To change the default display format, change the setting of DBTIME, or of the GL_DATETIME and USE_DTENV environment variables.

In East Asian locales that support era-based dates, DBTIME can also specify Japanese eras. See HCL OneDB GLS User's Guide for details of additional placeholder symbols for setting DBTIME to display era-based DATETIME values, and for descriptions of the GL_DATETIME, GL_DATE, and USE_DTENV environment variables.