Converting Alphanumeric Dates to WebFOCUS Dates

In this section:

Reference:

In some data sources, date values are stored in alphanumeric format without any particular standard, with any combination of components, such as year, quarter, and month, and with any delimiter. In a sorted report, if such data is sorted alphabetically, the sequence does not make business sense. To ensure adequate sorting, aggregation, and reporting on date fields, WebFOCUS can convert the alphanumeric dates into standard WebFOCUS date format using a conversion pattern that you can specify in the Master File attribute called DATEPATTERN.

Each element in the pattern is either a constant character which must appear in the actual input or a variable that represents a date component. You must edit the USAGE attribute in the Master File so that it accounts for the date elements in the date pattern. The maximum length of the DATEPATTERN string is 64.

Reference: Usage Notes for DATEPATTERN

  • If your original date has elements with no WebFOCUS USAGE format equivalent, the converted date will not look like the original data. In that case, if you want to display the original data, you may be able to use an OCCURS segment to redefine the field with the original alphanumeric format and display that field in the request.
  • DATEPATTERN requires an ACTUAL format to USAGE format conversion. Therefore, it is not supported for SUFFIX=FOC and SUFFIX=XFOC data sources.

Specifying Variables in a Date Pattern

How to:

The valid date components (variables) are year, quarter, month, day, and day of week. In the date pattern, variables are enclosed in square brackets (these brackets are not part of the input or output. Note that if the data contains brackets, you must use an escape character in the date pattern to distinguish the brackets in the data from the brackets used for enclosing variables).

Syntax: How to Specify Years in a Date Pattern

[YYYY]

Specifies a four-digit year.

[YYYY]

Specifies a four-digit year.

[YY]

Specifies a two-digit year.

[yy]

Specifies a zero-suppressed two-digit year (for example, 8 for 2008).

[by]

Specifies a blank-padded two-digit year.

Syntax: How to Specify Month Numbers in a Date Pattern

[MM]

Specifies a two-digit month number.

[mm]

Specifies a zero-suppressed month number.

[bm]

Specifies a blank-padded month number.

Syntax: How to Specify Month Names in a Date Pattern

[MON]

Specifies a three-character month name in upper case.

[mon]

Specifies a three-character month name in lower case.

[Mon]

Specifies a three-character month name in mixed case.

[MONTH]

Specifies a full month name in upper case.

[month]

Specifies a full month name in lower case.

[Month]

Specifies a full month name in mixed case.

Syntax: How to Specify Days of the Month in a Date Pattern

[DD]

Specifies a two-digit day of the month.

[dd]

Specifies a zero-suppressed day of the month.

[bd]

Specifies a blank-padded day of the month.

Syntax: How to Specify Julian Days in a Date Pattern

[DDD]

Specifies a three-digit day of the year.

[ddd]

Specifies a zero-suppressed day of the year.

[bdd]

Specifies a blank-padded day of the year.

Syntax: How to Specify Day of the Week in a Date Pattern

[WD]

Specifies a one-digit day of the week.

[DAY]

Specifies a three-character day name in upper case.

[day]

Specifies a three-character day name in lower case.

[Day]

Specifies a three-character day name in mixed case.

[WDAY]

Specifies a full day name in upper case.

[wday]

Specifies a full day name in lower case.

[Wday]

Specifies a full day name in mixed case.

For the day of the week, the WEEKFIRST setting defines which day is day 1.

Syntax: How to Specify Quarters in a Date Pattern

[Q]

Specifies a one-digit quarter number (1, 2, 3, or 4).

For a string like Q2 or Q02, use constants before [Q], for example, Q0[Q].

Specifying Constants in a Date Pattern

Between the variables, you can insert any constant values.

If you want to insert a character that would normally be interpreted as part of a variable, use the backslash character as an escape character. For example:

For a single quotation mark, use two consecutive single quotation marks ('').

Sample Date Patterns

If the date in the data source is of the form CY 2001 Q1, the DATEPATTERN attribute is: 

DATEPATTERN = 'CY [YYYY] Q[Q]'

If the date in the data source is of the form Jan 31, 01, the DATEPATTERN attribute is: 

DATEPATTERN = '[Mon] [DD], [YY]'

If the date in the data source is of the form APR-06, the DATEPATTERN attribute is: 

DATEPATTERN = '[MON]-[YY]'

If the date in the data source is of the form APR - 06, the DATEPATTERN attribute is: 

DATEPATTERN = '[MON] - [YY]'

If the date in the data source is of the form APR '06, the DATEPATTERN attribute is: 

DATEPATTERN = '[MON] ''[YY]'

If the date in the data source is of the form APR [06], the DATEPATTERN attribute is: 

DATEPATTERN = '[MON] \[[YY]\]' (or '[MON] \[[YY]]'

Note that the right square bracket does not have to be escaped.

Example: Sorting By an Alphanumeric Date

In the following example, date1.ftm is a sequential file containing the following data: 

June 1, '02      
June 2, '02      
June 3, '02      
June 10, '02   
June 11, '02     
June 12, '02     
June 20, '02     
June 21, '02     
June 22, '02     
June 1, '03      
June 2, '03      
June 3, '03      
June 10, '03     
June 11, '03     
June 12, '03     
June 20, '03     
June 21, '03     
June 22, '03     
June 1, '04      
June 2, '04      
June 3, '04      
June 4, '04      
June 10, '04     
June 11, '04     
June 12, '04     
June 20, '04     
June 21, '04     
June 22, '04

In the DATE1 Master File, the DATE1 field has alphanumeric USAGE and ACTUAL formats, each A18: 

FILENAME=DATE1   , SUFFIX=FIX ,
  DATASET = c:\tst\date1.ftm    , $
  SEGMENT=FILE1, SEGTYPE=S0, $
    FIELDNAME=DATE1, ALIAS=E01, USAGE=A18, ACTUAL=A18, $

The following request sorts by the DATE1 FIELD: 

TABLE FILE DATE1
PRINT DATE1 NOPRINT
BY DATE1
ON TABLE SET PAGE NOPAGE
END

The output shows that the alphanumeric dates are sorted alphabetically, not chronologically: 

DATE1
-----
June 1, '02         
June 1, '03         
June 1, '04         
June 10, '02        
June 10, '03        
June 10, '04        
June 11, '02        
June 11, '03        
June 11, '04        
June 12, '02        
June 12, '03        
June 12, '04        
June 2, '02         
June 2, '03         
June 2, '04         
June 20, '02        
June 20, '03        
June 20, '04        
June 21, '02        
June 21, '03        
June 21, '04        
June 22, '02        
June 22, '03        
June 22, '04        
June 3, '02         
June 3, '03         
June 3, '04         
June 4, '04

In order to sort the data correctly, you can add a DATEPATTERN attribute to the Master File that enables WebFOCUS to convert the date to a WebFOCUS date field. You must also edit the USAGE format to make it a WebFOCUS date format. To construct the appropriate pattern, you must account for all of the components in the stored date. The alphanumeric date has the following variables and constants:

  • Variable: full month name in mixed case, [Month].
  • Constant: blank space.
  • Variable: zero-suppressed day of the month number, [dd].
  • Constant: comma followed by a blank space followed by an apostrophe (coded as two apostrophes in the pattern).
  • Variable: two-digit year, [YY].

The edited Master File follows. Note the addition of the DEFCENT attribute to convert the two-digit year to a four-digit year: 

FILENAME=DATE1   , SUFFIX=FIX ,
  DATASET = c:\tst\date1.ftm    , $
  SEGMENT=FILE1, SEGTYPE=S0, $
    FIELDNAME=DATE1, ALIAS=E01, USAGE=MtrDYY, ACTUAL=A18,
      DEFCENT=20,  
      DATEPATTERN = '[Month] [dd], ''[YY]', $

Now, issuing the same request produces the following output. Note that DATE1 has been converted to a WebFOCUS date in MtrDYY format (as specified in the USAGE format): 

DATE1
-----
June  1, 2002       
June  2, 2002       
June  3, 2002       
June 10, 2002       
June 11, 2002       
June 12, 2002       
June 20, 2002       
June 21, 2002       
June 22, 2002       
June  1, 2003       
June  2, 2003       
June  3, 2003       
June 10, 2003       
June 11, 2003       
June 12, 2003       
June 20, 2003       
June 21, 2003       
June 22, 2003       
June  1, 2004       
June  2, 2004       
June  3, 2004       
June  4, 2004       
June 10, 2004       
June 11, 2004       
June 12, 2004       
June 20, 2004       
June 21, 2004       
June 22, 2004