Date Conversion With OCONV

Getting an internal date into a suitable external format is easy, except that there are so many options from which to choose. Let us examine the second argument of OCONV for a date conversion.

The first character is always "D". This indicates that OCONV is performing a date conversion, as opposed to any of the multitude of other kinds of conversion this incredibly powerful function can perform. "D" alone is sufficient. This generates the default external format for your locale. This, in turn, is defined in the msg.text file in the UV account directory (if NLS (National Language Support) is not enabled) or in the TIME category of the locale (if NLS is enabled). For example:

    OCONV(DATE(), "D")

returns the current date, in whatever your default format is. You can verify this on the command line. For example:

    LIST VOC EVAL "OCONV(DATE(), 'D')" FMT "30L" ID.SUP FIRST 1
    SELECT EVAL "OCONV(DATE(), 'D')" FMT '30L' FROM VOC FIRST 1;
    SELECT [[CURRENT_DATE]] CONV 'D' FMT '30L' FROM VOC FIRST 1;

Following the "D" may appear:

• the number of digits in the year (default 4)
• the delimiter character to be used (default " ")
• the letter "E" to indicate "European" format (day month year order)

For example:

    OCONV(DATE(), "D2/")

Next may appear some date component specifiers.

• D = day number
• W = weekday name
• M = month
• Q = quarter
• Y = year
• J = ordinal number of day in year (Julian)

If any of these is used, then only the indicated date components appear. For example:

    OCONV(13000, "DMY")

returns only the month and year components, in this case "8 2003".

Date component specifiers can be further qualified.

• A = alphabetic form (for example WA, MA, YA)
• B = abbreviated form (for example WB, MB)
• L = use lower case (actually title case)
• R = Roman numerals (for example MR)

For example:

    OCONV(13000, "DDMB")

returns "04 AUG" (the day number and the abbreviated form of the month name)

Following all of that can appear formatting rules, that can specify how each component is to be formatted, for example leading zero or zero-suppressed numbers, length of alphabetic strings, and so on, as well as any literal text to be included between components. The formatting rules are encased in square brackets. For example:

    OCONV(13000, "D-YMD[4,2,2]")

returns "2003-08-04" (four digit year, two digit month, two digit day).

   OCONV(13000, "D DMY[Z,A2,2]")

returns "4 AU 03" (zero-suppressed day number, two character alphabetic month name, two digit year).

Combining all of the above, one can generate fairly impressive output. For example:

    OCONV(13000, "DWAMADYL[', ',' ',', ']")

returns "Monday, August 04, 2003". Items in quotes in the formatting specification are literal text to be placed between the generated date components. Note, too, that the "L" has changed the default "all upper case" in the resulting date.

STATUS()

Following OCONV the STATUS() function returns one of three possible values.

• 0 = the conversion was successful
• 1 = the first argument was invalid (for dates, this means that it was not a number - non-integer numbers are truncated as part of date conversion)
• 2 = the second argument was invalid (for dates, this means that it was not a valid date conversion specification)

Failed Conversion

The result returned by OCONV for failed date (and other) conversion depends on account flavor and/or the $OPTIONS INFO.CONVERT setting currently in force. By default, in non-Pick flavors a zero-length string is returned, while in Pick flavors the original string is returned.

Multiple Conversion

It is not possible to generate the form YYYYMMDD - a delimiter character, perhaps a space, will always be inserted. However the Oconv() function accepts a multi-valued specification as its second argument. Therefore, for example, to produce the date in YYYYMMDD format (no delimiters), the following might be employed:

   OCONV(13000, "DYMD[4,2,2]" : @VM : "MCN")

This is functionally equivalent to:

   OCONV(OCONV(13000, "DYMD[4,2,2]"), "MCN")

(The "MCN" conversion preserves numeric characters and discards all others.)

Back to Date Conversion Demystified

This material is copyright © 2003, Ray Wurlod. All rights reserved.