[Bug]: SQL Server date/time DDL does not preserve source types

[fskorgen]

Apache Hop version?

2.19

Java version?

21

Operating system

Windows

What happened?

Affected: 2.19.0 and earlier.

MsSqlServerDatabaseMeta.getFieldDefinition() emits DATETIME for both Hop Date and Timestamp:

case IValueMeta.TYPE_TIMESTAMP, IValueMeta.TYPE_DATE:
  retval += "DATETIME";
  break;

The mapping cannot round-trip SQL Server's current temporal types:

Source column	JDBC/Hop metadata	Generated DDL	Change
date	Types.DATE / Date	DATETIME	date-only becomes date and time
time	Types.TIME / Date	DATETIME	time-only becomes date and time
datetime2(7)	Date or Timestamp	DATETIME	lower precision and smaller range

This is not only a naming difference. SQL Server datetime starts at 1753 and rounds to 3.33 ms,
while datetime2 starts at year 0001 and supports 100 ns precision. See Microsoft's
date and time data types.

Steps to reproduce

1. Create a SQL Server table with a date, a time and a datetime2(7) column.
2. Read it and generate the table DDL from the resulting row metadata — Table Output's SQL
   button, or MsSqlServerDatabaseMeta.getFieldDefinition() directly.

Expected: DATE, TIME and DATETIME2 — the table can be recreated from the metadata that was
read.
Actual: DATETIME for all three. Reproduced against 2.19.0 by passing the metadata read for
these columns to getFieldDefinition(). The native SQL Server meta inherits the same method.

Suggested fix

Use IValueMeta.getOriginalColumnType() when it is available so Types.DATE and Types.TIME
generate DATE and TIME. Generate DATETIME2 for Hop Timestamp and use it as the modern fallback
where the original SQL type cannot be recovered. Add round-trip tests for all three source types and
both SQL Server connection variants.

Issue Priority

Priority: 2

Issue Component

Component: Database

[hansva]

Could you do these types of investigations against the current codebase, we don't do patches on existing code and 2.20 has a lot of changes around DDL handling