You are here: EDQ Applications > Director > Processor Library > Convert Number to Date

Convert Number to Date

The Convert Number to Date processor transforms numeric values that actually represent date values into formal Date attributes.

Use

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.

Configuration

Inputs

One or more Number attributes for conversion to a Date type. String and Date attributes are not valid inputs.

Note: 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

Option	Type	Purpose	Default Value
Base Date	Date	Sets the Base Date from which numbers will be counted in the specified units, to calculate a Date value.	31-Dec-1899 00:00:00
Convert from Excel data?	Yes/No	This option should be 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. See Note below.	No
Input Date Format	Selection (# of days/seconds/milliseconds from Base Date)	Sets the number of units, used in combination with the Base Date value to calculate Dates from numeric values.	# of days from Base Date
Treat Nulls as successful	Yes/No	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.	Yes
Time zone	Time Zone	Sets the time zone to use when converting numbers to date values.	Director Time Zone

Note: 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.

Outputs

Data attributes

Data attribute	Type	Purpose	Value
[Attribute Name].NumberToDate	Added	A new Date attribute with the calculated Date values.	The Date value calculated from the input Number value, using the specified configuration.

Flags

Flag

Purpose

Possible Values

NumberToDateSuccess

Flags whether or not the Number->Date conversion was successful for all records.

Y - Conversion was successful

N - Conversion was unsuccessful

Execution

Execution Mode	Supported
Batch	Yes
Real time Monitoring	Yes
Real time Response	Yes

Results Browsing

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

Output Filters

The following output filters are available from the Convert Number to Date processor:

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: