Date and DateTime String Handling in EazyDI

EazyDI provides seamless handling of date and datetime strings, particularly when sourced from various file systems such as flat files, Azure data lake, AWS S3, FTP, and Excel files. When these date or datetime strings are mapped to target columns in databases or similar applications, our app automatically attempts to convert them to date or datetime objects.

Date Format Inference

The app's conversion mechanism involves inferring the date format. By default, specially for ambiguous dates like MM/dd/yyyy or dd/MM/yyyy and similar others where both day and month are less than 12 and can be interchangeable, it follows the month-first format. However, if it encounters an entry that cannot be interpreted as month-first, such as '21-01-2024', it switches to day-first format. This flexibility ensures smooth handling of date strings and also conforms to ISO 8601 standards, which are prevalent in API connectors and aligns with the US standard where the month usually precedes the day.

User-Defined Formats

While our app intelligently infers date formats, users also have the option to define the format explicitly. This can be achieved by utilizing the TO_DATE expression string on the column level. Supported formats include:

• 'MM/dd/yyyy'

• 'MM-dd-yyyy'

• 'MM.dd.yyyy'

• 'dd/MM/yyyy'

• 'dd-MM-yyyy'

• 'dd.MM.yyyy'

• 'MM/dd/yyyy HH:mm'

• 'MM-dd-yyyy HH:mm'

• 'MM.dd.yyyy HH:mm'

• 'dd/MM/yyyy HH:mm'

• 'dd-MM-yyyy HH:mm'

• 'dd.MM.yyyy HH:mm'

• 'MM/dd/yyyy HH:mm:ss'

• 'MM-dd-yyyy HH:mm:ss'

• 'MM.dd.yyyy HH:mm:ss'

• 'dd/MM/yyyy HH:mm:ss'

• 'dd-MM-yyyy HH:mm:ss'

• 'dd.MM.yyyy HH:mm:ss'

• 'yyyy-MM-dd'

• 'yyyy-MM-dd HH:mm'

• 'yyyy-MM-dd HH:mm:ss'

• 'yyyy-MM-ddTHH:mm:ss.SSSZ'

• 'dd-MMM-yyyy'

• 'dd-MMM-yyyy HH:mm'

• 'dd-MMM-yyyy HH:mm:ss'

• 'Epoch timestamp in milliseconds-13 digits' (since v1.2.0)

• 'Epoch timestamp in microseconds-16 digits' (since v1.2.0)

• 'Epoch timestamp in seconds-10 digits' (since v1.2.0)

These formats provide users with flexibility and control over how date and datetime strings are interpreted and converted within the application.

Example:

1. We use an excel connector as a source and an rdbms application as our target

Open

2. We use the TO_DATE expression string for sample_Date and choose the appropriate date format

Open

3. Once the pipeline runs, you will see using the TO_DATE expression string has converted the string to a date object based on the chose format

Note:

For files that have meta data like Excel, if we set the column as date like below

Then EazyDI will be able to infer the column as date or date time and even if we use the TO_DATE function, it will skip it as it only transforms columns which are of string types