I am fetching a column which has timestamp value in the format of YYYY-MM-DD hh:mm:ss.(fff) and am trying to use this column to convert as timestamp TIMESTAMP(TIMESTAMP#(data_loaded_date,'YYYY-MM-DD hh:mm:ss.(fff)')
Unfortunately timestamp# works fine but when I add timestamp on top of timestamp# it returns null value.Any one has faced this kind of issue ? Or is it a bug ? Could anyone help me on this
I need to fetch latest modified records for which I need to convert data_loaded_date as a timestamp and use max of it. Please advice
Are you sure the Timestamp#() is working correctly? If it is, you should be able to view the underlying field as both text (the timestamp format) and as a number (the underlying date/time, e.g. 42321.124), so formatting it as a number should let you see if it's actually getting the correct result as you expect.
Guys, Yes timestamp# returns correct value. Sample value is as below
2021-07-06 13:12:34.576940000
Created dimesion is
TIMESTAMP(TIMESTAMP#(data_loaded_date,'YYYY-MM-DD hh:mm:ss.(fff)') ) as formatedts
I am using timestamp on top of timestamp# because I need to load records based on latest timestamp . To find latest timestamp I am using max fucntion like max(formatedts)
When I use timestamp# only the values are as expected but still max fucntion returns null value
I attempted to use TimeStamp#() on your sample value and it does not work on my end. I am not getting a timestamp value, just the original value as text. However, when using your sample, I am able to use the value directly as a timestamp - TIMESTAMP(data_loaded_date,'YYYY-MM-DD hh:mm:ss.(fff)') works correctly.
