I have a CSV file with datetime values like ‘2020-02-02 04:00:00.0000000’ when I try to convert/cast these to TIMESTAMPS I get really weird results. Sometimes the TO_TIMESTAMP return NULLs when CAST as TIMESTAMP returns the correct result.
LEFT(DepartureTime, LENGTH(DepartureTime) - 4) as DepTimeTrimmedText,
TO_TIMESTAMP(LEFT(DepartureTime, LENGTH(DepartureTime) - 4), 'YYYY-MM-DD HH:MI:SS.FFF', 1) as DepTimeToTimeStamp,
CAST(LEFT(DepartureTime, LENGTH(DepartureTime) - 4) AS TIMESTAMP) as "DepTimeCast"
FROM "MCT Data Lake".raw.TOS.ContainerVisit
@jeffreyslort The Dremio TO_TIMESTAMP takes up to milli seconds (FFF), that’s why you are getting null
Thanks for your reply.
I know it only takes timestamps up to milliseconds in precision. Thats why I remove all “additional” digits so we are left with a timestamp string with only 3 digits after the decimal point.
What I find very peculiar is that with these equally formatted strings TO_TIMESTAMP returns NULL 50% of the time while
CAST AS DATE does not. While the documentation states that TO_TIMESTAMP should work the same as
CAST AS DATE.
@jeffreyslort What version of Dremio is this?