fskorgen opened a new issue, #8220:
URL: https://github.com/apache/hop/issues/8220
### 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:
```java
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](https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql).
### 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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
