[Bug]: Consistent Date Handling Issues with SQLite Plugin in Apache Hop

[AlefRP]

Apache Hop version?

2.8.0

Java version?

OpenJDK 21.0.3 LTS

Operating system

Windows

What happened?

Environment

• Apache Hop Version: 2.8.0 and 2.7.0
• SQLite Version: 3.43.0 2023-08-24 (32-bit)
• Java Version: OpenJDK 21.0.3 LTS, Temurin-21.0.3+9, built 2024-04-16
• Operating System: Windows 11 Pro, Version 23H2, OS Build 22631.3527, Windows Feature Experience Pack 1000.22700.1003.0

Issue Description

Apache Hop consistently struggles with handling date data from SQLite databases. This includes failures and incorrect results when using various date conversion functions (STRFTIME, DATE, FORMAT) as well as issues with direct imports of date columns that contain null values. In other database clients like DBeaver, these dates are displayed and processed correctly, but Apache Hop converts date values to integers or fails to properly recognize and import them when no conversion function is applied.

Steps to Reproduce

1. Direct Import of Date Columns:

   • Import a date column directly from a SQLite database into Apache Hop without any conversion functions.
   • Observe how Apache Hop handles the raw date data, especially focusing on the handling of null values.

2. Using Date Conversion Functions:

   • Apply various date conversion functions (STRFTIME, DATE, FORMAT) to a date column in separate transformations.
   • Check the output in each case and note any discrepancies or failures, particularly when the input data contains null or empty values.

Expected Result

• Directly imported date columns should be recognized and displayed correctly as date data types.
• Date conversion functions should return properly formatted date strings, and handle null values gracefully without causing failures.

Actual Result

• Directly imported dates, especially those with null values, are not recognized or handled properly.
• Date conversion functions return integers (primarily the year component) instead of formatted date strings and struggle with null date values.

Additional Information

• Screenshots and logs that show the discrepancies in date handling in Apache Hop compared to expected outputs observed in other environments can be attached here.

Issue Priority

Priority: 1

Issue Component

Component: Database