IRI Blog Articles

Diving Deeper into Data Management

 

 

Date Format Masking

by Jason Koivu

Screen Shot to Show Date Format Masking Options

It is rare that you would find an application or database that does not rely on some sort of date information. Transactions are often time/date stamped and many data entry records are comprised of information, e.g., birth dates, death dates, start and finish dates.

Date format masks provide a structure which uses specific characters and symbols that define the format of a field. The structures can be applied to both input and output data. In the input, a field must follow the specific pattern defined by the mask. In the output, the mask defines the format for converting from an input mask. For example, if a database expects a birth date in the format where the components are a two-digit month, two-digit day, and a four-digit year (MM/DD/YYYY), and October 4, 1975 is entered, the user will be unable to save the data until the correct format is entered, which would be 10/04/1975. The exact behavior depends on how the database administrator or data architect has defined the mask.

Input masks are used for a number of entry fields, but are most commonly used for numbers (phone numbers, social security numbers, model/parts numbers, etc.) and dates. A date is a more complex data type than a string or a number. It has multiple parts (year, month, day, hour, and so on), and there are many rules for determining valid values and calculations, e.g., leap days and years, daylight saving time changes, national and company holidays, and date ranges.

For IRI CoSort, we developed composite field syntax for the SortCL data definition and manipulation program to give users the ability to define and establish any date format they have, or need to re-cast, via a data format mask. A composite field contains a mix of data elements and literals. The elements can go on to become individual fields in other sections of the script for manipulation and mapping purposes. When a composite field is read, each data item is extracted and assigned to an internal field. On output, a composite value is constructed from internal fields, expressions, and literals. On record input and output, date and time values are valuated and impossible combinations cause an error message.

Consider the input numeric composite dates, numeric_dates:

5 03/29/13
4 12/02/71
2 10/12/10

Use the following SortCL job script, comp_dates.ncl, to reformat the input dates as composites:

/TEMPLATE=(Dates_WMDY = "%u %m/%e/%y") # numeric Week Month/Day/Year(2)
/TEMPLATE=(Dates_DMY = "%e-%m-%Y") # numeric Day-Month-Year(4)
/TEMPLATE=(Dates_Spanish= "&A %d de %B de %Y")
/INFILE=numeric_dates
  /FIELD=(In, POSITION=1, SIZE=10, STRUCTURE=Dates_WMDY(DofW,Month,Day,Year))

/REPORT

/OUTFILE=out_form
  /FIELD=(Out, STRUCTURE=Dates_DMY(Day,Month,Year))
/OUTFILE=out_SP
  /LOCALE= spanish
  /FIELD= (Out, STRUCTURE=Dates_Spanish(DofW,Day,Month,Year))

The output of this script would result in the following:

out_form
29-03-2013
2-12-1971
12-10-2010

out_SP
viernes 29 de marzo de 2013
jueves 02 de diciembre de 1971
martes 12 de octubre de 2010

The template syntax defines the parameters of the date format mask and each piece of data that is being read on input. The user can then choose whatever date format is required for the output. The user can even produce an output in multiple languages if necessary.

IRI supports other input masks* particularly for numeric formatting. Refer to section 11.12 in the SortCL program chapter of the CoSort v9.5.3 user manual for more information. See the tech tip in this newsletter for an example of the implied decimal attribute used in this context.

*Generally, data format (or input) masks facilitate data validation and prevent users from entering invalid data (such as a phone number in a date field). Such masks can also help ensure that users enter data in a consistent way. That consistency simplifies data identification and database maintenance. Note that masking the format of the data, in this case, differs from data masking in the data privacy/security context.

Print Friendly

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: