An Analysis With a CAST AS TIMESTAMP Expression Fails With a Snowflake Datasource: Datetime value does not match the specified format (Doc ID 2937949.1)

Last updated on APRIL 04, 2023

Applies to:

Symptoms

On Oracle Analytics Server (OAS), the RPD has a Logical Column with a custom expression that converts a concatenated "date" and "time" value to TIMESTAMP. For example:-

CAST( (CAST((CASE WHEN <expression> THEN NULL ELSE <"DAY_DT" column> END) AS CHAR)
            ||
            (CASE WHEN (CASE WHEN <expression> THEN NULL ELSE <"DAY_DT" column> END) IS NULL THEN NULL ELSE <"HH24MISS_STR" column> END)
        ) AS TIMESTAMP)

The data source is a Snowflake database where the "HH24MISS_STR" column is populated with text strings / character values. For example: '12:34:56', '7:46:23', '0:00:00' etc.

An analysis that references the column may fail when run and an error such as the following is observed:

          [nQSError: 59140] The evaluation of the index 1 expression on row count -1 failed
         [nQSError: 46046] Datetime value 2021/01/14 7:46:23 does not match the specified format

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.