String to date conversion errors

[SamGreene]

Hi,

I am getting data from CDC on SQL Server using Informatica which is writing parquet files to ADLS.  I read the parquet files using DLT and end up with the date data as a string such as this

'20240603164746563'

I couldn't get this to convert using milliseconds without a strict parser error that says to use the legacy option, which is not available to use. So I did a LEFT on the data to get rid of the MS and get this:

,TO_TIMESTAMP(LEFT(event_time,14), 'yyyyMMddHHmmss') AS event_datetime

Then I convert to my local timezone: 

   ,CONVERT_TIMEZONE('UTC', 'America/Phoenix',TO_TIMESTAMP(LEFT(event_time,14), 'yyyyMMddHHmmss')) 

When I run the DLT pipeline I get this error:

Your table schema requires manually enablement of the following table feature(s): timestampNtz.

ALTER TABLE table_name SET TBLPROPERTIES ('delta.feature.feature_name' = 'supported')

However, I cannot run this code as it is a Live table. 

Someone please help me out as I feel like I'm really missing something by having so much trouble with what should be an easy task!

[Kaniz_Fatma]

Hi @SamGreene, You’re on the right track with using the TO_TIMESTAMP function. However, you might be encountering issues due to the format of your timestamp string. The string ‘20240603164746563’ seems to represent a timestamp down to the millisecond. If you’re only interested in the second-level precision, you can indeed use the LEFT function to trim the string. 

The error message you’re seeing suggests that your table schema requires the timestampNtz feature to be manually enabled. This feature is used for handling timestamps without timezone information. However, since you’re working with a Live Table, you’re unable to run the ALTER TABLE command to enable this feature.

It might be worth filing a Databricks support ticket for more specific guidance on this issue.