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'

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

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

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