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!

[wt-asw]

I am also running into a similar problem. did you ever resolve this?

[IdleGuys]

@wt-asw I ran into the same problem, I have provided the table properties at the table declaration as below. Hope this helps! 

CREATE OR REFRESH LIVE TABLE silver_table_name_mv
TBLPROPERTIES ('delta.feature.timestampNtz' = 'supported')

AS 

SELECT
...
;

[SamGreene]

Checking on my current code, this is what I am using, which works for me because we don't use daylight savings time.  

from_utc_timestamp(date_time_utc, 'UTC-7') as date_time_local