Convert Number to Date

1.3.10.10 Convert Number to Date

The Convert Number to Date processor transforms Number or Number Array values that actually represent date values into the formal Date or Date Array type respectively.

Dates are often internally stored in databases as numbers, counted as a number of units (days, seconds, or milliseconds) from a given base date and time.

The formatting of these values as date or date/time values is often done using functions to retrieve the numeric values and present them as dates.

Depending on the way in which data is extracted from a source database, these date values may be captured as numbers. If EDQ only has access to the database extract, and not to the source database, it will therefore snapshot the values as numbers. It is then necessary to convert the numbers to a standard date format in order to process the dates correctly.

The Convert Number to Date processor, therefore, uses a configured base date, and a number of units, to calculate Date values from numeric values.

The following table describes the configuration options:

Configuration	Description
Inputs	Specify one or more Number or Number Array attributes for conversion to a Date or Date Array type. String and Date attributes are not valid inputs. If multiple attributes are submitted for conversion and one fails the entire record is marked as having failed, although the valid attributes will be correctly converted.
Options	Specify the following options: `Base Date`: sets the Base Date from which numbers will be counted in the specified units, to calculate a Date value. Specified as a Date. Default value: `31-Dec-1899 00:00:00`. `Convert from Excel data?`: used when converting numbers from Microsoft Excel spreadsheets that represent dates. It overcomes a known Microsoft issue where Excel incorrectly assumes that the year 1900 was a leap year. If the numeric values being processed originated from Microsoft Excel, values representing dates after (and including) the year 1900 will be incorrectly converted unless this option is set to Yes. See Microsoft's support article explaining this issue. Specified as `Yes`/`No`. Default value is `No`. `Input Date Format`: sets the number of units, used in combination with the Base Date value to calculate Dates from numeric values. Specified as a Selection (`# of days`/`seconds`/`milliseconds` `from Base Date`). Default value: `# of days from Base Date`. `Treat Nulls as successful`: For non-mandatory attributes, sets whether or not Null input values (for which a Null output value will be output) should be treated as successfully transformed. Specified as `Yes`/`No`. Default value is `Yes`. `Time zone`: sets the time zone to use for when converting numbers to date values. Specified as a time zone. Default value is the Director Time Zone.
Outputs	Describes any data attribute or flag attribute outputs.
Data Attributes	The following data attributes are output: `[Attribute Name].NumberToDate`: stores the result of the Number to Date conversion. Value is the Date value calculated from the input Number value, using the specified configuration.
Flags	The following flags are output: `NumberToDateSuccess`: flags whether or not the Number to Date conversion was successful for all records. Possible values: `Y`/`N`.

The following table describes the statistics produced by the profiler:

Statistic	Description
Successful	The number of records where the Number to Date conversion was successful (that is, a Date was calculated).
Unsuccessful	The number of records where the Number to Date conversion was unsuccessful (that is, a Date could not be calculated).

Output Filters

The following output filters are available:

Records where conversion was successful
Records where conversion was unsuccessful

Example

In this example, date values have been wrongly formatted as numbers in an Excel spreadsheet. The EDQ user has read-only access to the spreadsheet so cannot change the formatting, so converts the numbers to dates using this processor and the default configuration:

DateOfBirth	DateOfBirth.NumberToDate
18639	11-Jan-1951 00:00:00
19003	10-Jan-1952 00:00:00
17126	20-Nov-1946 00:00:00
28885	30-Jan-1979 00:00:00
{28885}{24800}	{30-Jan-1979 00:00:00}{24-Nov-1967 00:00:00}