topic Re: String to date conversion errors in Get Started Discussions

String to date conversion errors

SamGreene — Mon, 03 Jun 2024 22:27:25 GMT

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!

Re: String to date conversion errors

wt-asw — Fri, 10 Jan 2025 10:34:10 GMT

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

Re: String to date conversion errors

IdleGuys — Sat, 11 Jan 2025 17:28:08 GMT

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

Re: String to date conversion errors

SamGreene — Tue, 14 Jan 2025 17:44:23 GMT

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