DATETRAN: Formatting Dates in International Formats

How to:

Reference:

Available Languages: reporting, Maintain

The DATETRAN function formats dates in international formats.

Syntax: How to Format Dates in International Formats

DATETRAN (indate, '(intype)', '([formatops])', 'lang', outlen, output)

where:

indate

Is the input date (in date format) to be formatted. Note that the date format cannot be an alphanumeric or numeric format with date display options (legacy date format).

intype

Is one of the following character strings indicating the input date components and the order in which you want them to display, enclosed in parentheses and single quotation marks.

The following table shows the single component input types:

Single Component Input Type

Description

'(W)'

Day of week component only (original format must have only W component).

'(M)' 

Month component only (original format must have only M component).

The following table shows the two-component input types:

Two-Component Input Type

Description

'(YYM)'

Four-digit year followed by month.

'(YM)'

Two-digit year followed by month.

'(MYY)'

Month component followed by four-digit year.

'(MY)'

Month component followed by two-digit year.

The following table shows the three-component input types:

Three-Component Input Type

Description

'(YYMD)'

Four-digit year followed by month followed by day.

'(YMD)' 

Two-digit year followed by month followed by day.

'(DMYY)'

Day component followed by month followed by four-digit year.

'(DMY)'

Day component followed by month followed by two-digit year.

'(MDYY)'

Month component followed by day followed by four-digit year.

'(MDY)'

Month component followed by day followed by two-digit year.

'(MD)'

Month component followed by day (derived from three-component date by ignoring year component).

'(DM)'

Day component followed by month (derived from three-component date by ignoring year component).

formatops

Is a string of zero or more formatting options enclosed in parentheses and single quotation marks. The parentheses and quotation marks are required even if you do not specify formatting options. Formatting options fall into the following categories:

  • Options for suppressing initial zeros in month or day numbers.

    Note: Zero suppression replaces initial zeros with blanks spaces.

  • Options for translating month or day components to full or abbreviated uppercase or default case (mixed-case or lowercase depending on the language) names.
  • Date delimiter options and options for punctuating a date with commas.

Valid options for suppressing initial zeros in month or day numbers are listed in the following table. Note that the initial zero is replaced by a blank space:

Format Option

Description

m

Zero-suppresses months (displays numeric months before October as 1 through 9 rather than 01 through 09).

d

Displays days before the tenth of the month as 1 through 9 rather than 01 through 09.

dp

Displays days before the tenth of the month as 1 through 9 rather than 01 through 09 with a period after the number.

do

Displays days before the tenth of the month as 1 through 9. For English (langcode EN) only, displays an ordinal suffix (st, nd, rd, or th) after the number.

The following table shows valid month and day name translation options:

Format Option

Description

T

Displays month as an abbreviated name, with no punctuation, all uppercase.

TR

Displays month as a full name, all uppercase.

Tp

Displays month as an abbreviated name, followed by a period, all uppercase.

t

Displays month as an abbreviated name with no punctuation. The name is all lowercase or initial uppercase, depending on language code.

tr

Displays month as a full name. The name is all lowercase or initial uppercase, depending on language code.

tp

Displays month as an abbreviated name, followed by a period. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).

W

Includes an abbreviated day-of-the-week name at the start of the displayed date, all uppercase with no punctuation.

WR

Includes a full day-of-the-week name at the start of the displayed date, all uppercase.

Wp

Includes an abbreviated day-of-the-week name at the start of the displayed date, all uppercase, followed by a period.

w

Includes an abbreviated day-of-the-week name at the start of the displayed date with no punctuation. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).

wr

Includes a full day-of-the-week name at the start of the displayed date. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).

wp

Includes an abbreviated day-of-the-week name at the start of the displayed date followed by a period. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).

X

Includes an abbreviated day-of-the-week name at the end of the displayed date, all uppercase with no punctuation.

XR

Includes a full day-of-the-week name at the end of the displayed date, all uppercase.

Xp

Includes an abbreviated day-of-the-week name at the end of the displayed date, all uppercase, followed by a period.

x

Includes an abbreviated day-of-the-week name at the end of the displayed date with no punctuation. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).

xr

Includes a full day-of-the-week name at the end of the displayed date. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).

xp

Includes an abbreviated day-of-the-week name at the end of the displayed date followed by a period. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).

The following table shows valid date delimiter options:

Format Option

Description

B

Uses a blank as the component delimiter. This is the default if the month or day of week is translated or if comma is used.

.

Uses a period (.) as the component delimiter.

-

Uses a minus sign (-) as the component delimiter. This is the default when the conditions for a blank default delimiter are not satisfied.

/

Uses a slash (/) as the component delimiter.

|

Omits component delimiters.

K

Uses appropriate Asian characters as component delimiters.

c

Places a comma (,) after the month name (following T, Tp, TR, t, tp, or tr).

Places a comma and blank after the day name (following W, Wp, WR, w, wp, or wr).

Places a comma and blank before the day name (following X, XR, x, or xr).

e

Displays the Spanish or Portuguese word de or DE between the day and month, and between the month and year. The case of the word de is determined by the case of the month name. If the month is displayed in uppercase, DE is displayed. Otherwise, de is displayed. Useful for formats DMY, DMYY, MY, and MYY.

D

Inserts a comma (,) after the day number and before the general delimiter character specified.

Y

Inserts a comma (,) after the year and before the general delimiter character specified.

lang

Is the two-character standard ISO code for the language into which the date should be translated, enclosed in single quotation marks ('). Valid language codes are:

  • 'AR' Arabic
  • 'CS' Czech
  • 'DA' Danish
  • 'DE' German
  • 'EN' English
  • 'ES' Spanish
  • 'FI' Finnish
  • 'FR' French
  • 'EL' Greek
  • 'IW' Hebrew
  • 'IT' Italian
  • 'JA' Japanese
  • 'KO' Korean
  • 'LT' Lithuanian
  • 'NL' Dutch
  • 'NO' Norwegian
  • 'PO' Polish
  • 'PT' Portuguese
  • 'RU' Russian
  • 'SV' Swedish
  • 'TH' Thai
  • 'TR' Turkish
  • 'TW' Chinese (Traditional)
  • 'ZH' Chinese (Simplified)
outlen

Numeric

Is the length of the output field in bytes. If the length is insufficient, an all blank result is returned. If the length is greater than required, the field is padded with blanks on the right.

output

Alphanumeric

Is the name of the field that contains the translated date, or its format enclosed in single quotation marks.

Reference: Usage Notes for the DATETRAN Function

  • The output field, though it must be type A, and not AnV, may in fact contain variable length information, since the lengths of month names and day names can vary, and also month and day numbers may be either one or two bytes long if a zero-suppression option is selected. Unused bytes are filled with blanks.
  • All invalid and inconsistent inputs result in all blank output strings. Missing data also results in blank output.
  • The base dates (1900-12-31 and 1900-12 or 1901-01) are treated as though the DATEDISPLAY setting were ON (that is, not automatically shown as blanks). To suppress the printing of base dates, which have an internal integer value of 0, test for 0 before calling DATETRAN. For example:
    RESULT/A40 = IF DATE EQ 0 THEN ' ' ELSE
                    DATETRAN (DATE, '(YYMD)', '(.t)', 'FR', 40, 'A40');
  • Valid translated date components are contained in files named DTLNGlng where lng is a three-character code that specifies the language. These files must be accessible for each language into which you want to translate dates.
  • For these NLS characters to appear correctly, the WebFOCUS Reporting Server and TIBCO WebFOCUS® Client must be configured with the correct code pages.
  • The DATETRAN function is not supported in Dialogue Manager.

Example: Using the DATETRAN Function

The following request prints the day of the week in the default case of the specific language:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20051003;
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
OUT1A/A8=DATETRAN(DATEW, '(W)', '(wr)', 'EN', 8 , 'A8') ;
OUT1B/A8=DATETRAN(DATEW2, '(W)', '(wr)', 'EN', 8 , 'A8') ;
OUT1C/A8=DATETRAN(DATEW, '(W)', '(wr)', 'ES', 8 , 'A8') ;
OUT1D/A8=DATETRAN(DATEW2, '(W)', '(wr)', 'ES', 8 , 'A8') ;
OUT1E/A8=DATETRAN(DATEW, '(W)', '(wr)', 'FR', 8 , 'A8') ;
OUT1F/A8=DATETRAN(DATEW2, '(W)', '(wr)', 'FR', 8 , 'A8') ;
OUT1G/A8=DATETRAN(DATEW, '(W)', '(wr)', 'DE', 8 , 'A8') ;
OUT1H/A8=DATETRAN(DATEW2, '(W)', '(wr)', 'DE', 8 , 'A8') ;
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT wr"
""
"Full day of week name at beginning of date, default case (wr)"
"English / Spanish / French / German"
""
SUM OUT1A AS '' OUT1B AS '' TRANSDATE NOPRINT
OVER OUT1C AS '' OUT1D AS ''
OVER OUT1E AS '' OUT1F AS ''
OVER OUT1G AS '' OUT1H AS '' 
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
GRID=OFF, $
END

The output is:

The following request prints a blank delimited date with an abbreviated month name in English. Initial zeros in the day number are suppressed, and a suffix is added to the end of the number:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
OUT2A/A15=DATETRAN(DATEYYMD,  '(MDYY)', '(Btdo)', 'EN', 15, 'A15') ;
OUT2B/A15=DATETRAN(DATEYYMD2, '(MDYY)', '(Btdo)', 'EN', 15, 'A15') ;
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Btdo"
""
"Blank-delimited (B)"
"Abbreviated month name, default case (t)"
"Zero-suppress day number, end with suffix (do)"
"English"
""
SUM OUT2A AS '' OUT2B AS '' TRANSDATE NOPRINT 
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a blank delimited date, with an abbreviated month name in German. Initial zeros in the day number are suppressed, and a period is added to the end of the number:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
OUT3A/A12=DATETRAN(DATEYYMD,  '(DMYY)', '(Btdp)', 'DE', 12, 'A12');
OUT3B/A12=DATETRAN(DATEYYMD2, '(DMYY)', '(Btdp)', 'DE', 12, 'A12');
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Btdp"
""
"Blank-delimited (B)"
"Abbreviated month name, default case (t)"
"Zero-suppress day number, end with period (dp)"
"German"
""
SUM OUT3A AS '' OUT3B AS '' TRANSDATE NOPRINT 
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a blank delimited date in French, with a full day name at the beginning and a full month name, in lowercase (the default for French):

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
OUT4A/A30 = DATETRAN(DATEYYMD,  '(DMYY)', '(Bwrtr)', 'FR', 30, 'A30');
OUT4B/A30 = DATETRAN(DATEYYMD2, '(DMYY)', '(Bwrtr)', 'FR', 30, 'A30');
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Bwrtr"
""
"Blank-delimited (B)"
"Full day of week name at beginning of date, default case (wr)"
"Full month name, default case (tr)"
"English"
""
SUM OUT4A AS '' OUT4B AS '' TRANSDATE NOPRINT 
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a blank delimited date in Spanish with a full day name at the beginning in lowercase (the default for Spanish), followed by a comma, and with the word “de” between the day number and month and between the month and year:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
OUT5A/A30=DATETRAN(DATEYYMD,  '(DMYY)', '(Bwrctrde)', 'ES', 30, 'A30');
OUT5B/A30=DATETRAN(DATEYYMD2, '(DMYY)', '(Bwrctrde)', 'ES', 30, 'A30');
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Bwrctrde"
""
"Blank-delimited (B)"
"Full day of week name at beginning of date, default case (wr)"
"Comma after day name (c)"
"Full month name, default case (tr)"
"Zero-suppress day number (d)"
"de between day and month and between month and year (e)"
"Spanish"
""
SUM OUT5A AS '' OUT5B AS '' TRANSDATE NOPRINT 
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a date in Japanese characters with a full month name at the beginning, in the default case and with zero suppression:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
OUT6A/A30=DATETRAN(DATEYYMD , '(YYMD)', '(Ktrd)', 'JA', 30, 'A30');
OUT6B/A30=DATETRAN(DATEYYMD2, '(YYMD)', '(Ktrd)', 'JA', 30, 'A30');
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Ktrd"
""
"Japanese characters (K in conjunction with the language code JA)"
"Full month name at beginning of date, default case (tr)"
"Zero-suppress day number (d)"
"Japanese"
""
SUM OUT6A AS '' OUT6B AS '' TRANSDATE NOPRINT 
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a blank delimited date in Greek with a full day name at the beginning in the default case, followed by a comma, and with a full month name in the default case:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
OUT7A/A30=DATETRAN(DATEYYMD , '(DMYY)', '(Bwrctr)', 'GR', 30, 'A30');
OUT7B/A30=DATETRAN(DATEYYMD2, '(DMYY)', '(Bwrctr)', 'GR', 30, 'A30');
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Bwrctrde"
""
"Blank-delimited (B)"
"Full day of week name at beginning of date, default case (wr)"
"Comma after day name (c)"
"Full month name, default case (tr)"
"Greek"
""
SUM OUT7A AS '' OUT7B AS '' TRANSDATE NOPRINT 
ON TABLE SET PAGE-NUM OFF
END

The output is: