3782346 - Sorting a view after to_timestamp conversion on decimal timestamps fails with "invalid date, time or timestamp value" in SAC Datasphere

A view uses TO_TIMESTAMP(STARTED_AT) to convert the field to a timestamp and the conversion appears to work.

However, When sorting on the converted timestamp field in data preview, an error occurs:

• Error message: "invalid DATE, TIME or TIMESTAMP value: SQL Error"

SAP Datasphere

1. Create or open a view that converts a DECIMAL timestamp column (for example, STARTED_AT) using TO_TIMESTAMP(...).
2. Open data preview and sort by the converted timestamp column.
3. Observe the error "invalid DATE, TIME or TIMESTAMP value: SQL Error".

Source data contains invalid timestamp values, for example, values ending with '...60' in YYYYMMDDHHMISS, which are not accepted by the timestamp conversion and cause failures when sorting.

1. Reproduce the issue in the Data Explorer directly by viewing data and sorting in the same view.
2. Run testing SELECT SQL statements in the source view/table to identify the problem source records that contain invalid timestamp values. For example:
   
   • SELECT "STARTED_AT", COUNT(*) AS CNTFROM <table name> WHERE "STARTED_AT" IS NOT NULL AND "STARTED_AT" <> 0 AND NOT TO_NVARCHAR(CAST("STARTED_AT" AS BIGINT)) LIKE_REGEXPR '^[0-9]{4}(0[1-9]|1[0-2])(0[1-9]|[12][0-9]|3[01])([01][0-9]|2[0-3])[0-5][0-9][0-5][0-9]$'GROUP BY "STARTED_AT"ORDER BY CNT DESC;
3. Once the problem source records are identified, correct these problem source records.
4. Check if the issue in the Datasphere view preview is resolved.

TO_TIMESTAMP Function (Data Type Conversion)

to_timestamp, decimal timestamp, date, time, dec-based timestamp, invalid DATE TIME or TIMESTAMP value, sorting error, data preview, views, order by, timestamp conversion, seconds=60, null handling, hana sql , KBA , DS-MD-VIW , Views , Problem