String to date conversion errors

SamGreene
Contributor III

‎06-03-2024 03:27 PM

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!
0 Kudos

wt-asw
New Contributor II

‎01-10-2025 02:34 AM

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

0 Kudos

IdleGuys
New Contributor II

‎01-11-2025 09:28 AM

@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
...
;

 

0 Kudos

SamGreene
Contributor III

‎01-14-2025 09:44 AM

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
0 Kudos