DBVisualizer displays null on date field holding '0001-01-01'

Question

I issued an SQL statement in DbVis:

select vestdate, name from person where vestdate is not null

And got many results where DbVisualizer showed vestdate as (null)!

After investigating, I discovered that the vestdate was '0001-01-01', so the query correctly returned these records, but DbVisualizer displays them as (null).

I have just switched from windows 8 to windows 10.
It works on windows 8 (displays '0001-01-01'), but not not windows 10 (displays null):

Product: DbVisualizer Pro 11.0.4 [Build #3103]
OS: Windows 8.1
OS Version: 6.3
OS Arch: amd64
Java Version: 1.8.0_252
Java VM: OpenJDK 64-Bit Server VM
Java Vendor: AdoptOpenJDK
Java Home: c:\program files\dbvisualizer\jre
DbVis Home: C:\Program Files\DbVisualizer
User Home: -------
PrefsDir: -------
SessionId: 55
BindDir: -------

Product: DbVisualizer Pro 11.0.5 [Build #3113]
OS: Windows 10
OS Version: 10.0
OS Arch: amd64
Java Version: 1.8.0_252
Java VM: OpenJDK 64-Bit Server VM
Java Vendor: AdoptOpenJDK
Java Home: c:\program files\dbvisualizer\jre
DbVis Home: C:\Program Files\DbVisualizer
User Home: -------
PrefsDir: -------
SessionId: 968
BindDir: -------

Any ideas how to make the program show me the real value, not the interpreted value of null?

Answer 1

The issue is explained in an IBM support document:

Problem

Trying to insert a date value into a date column before 1940 or after 2039 will represent the date as NULL within the respective database table.

Cause

This is caused by a limitation with the IBM i database Toolbox JDBC driver as detailed in the related link:

How does the Toolbox JDBC driver deal with dates before 1940 (or after 2039)? The IBM i database supports several date formats. The Toolbox JDBC driver uses the date format that is set up as the default on the IBM i system. This default is usually set to "mdy" which only supports dates between 1940 and 2039. You can override the date format by specifying the "date format" property when opening the JDBC connection. The best choice is "iso", which supports a full four-digit date. The easiest way to do this is to add ";date format=iso" to the end of the URL used when connecting to the database.

Resolving The Problem

Appending the ";date format=iso" to the host connection property for the applicable database via Preferences-> EGL-> SQL Database Connections will then show the respective dates correctly eg:

1939-01-01.

The issue can be fixed in DBVisualizer by doing the following:

1. Database -> Edit Database Connection(s)...
2. Select Properties tab
3. Select Driver Properties
4. Edit parameters date format and time format to be iso
5. Apply Changes
6. Disconnect and reconnect