floating-pointsnowflake-cloud-data-platformabapsap-erp

Emulating the CTCV_CONVERT_FLOAT_TO_DATE function in SQL or Python


Inside my Snowflake we are using sap tables as data source, in one of our SAP tables we have values stored in the format 0x1.34b049p24 which equals 02/17/2023.

Now the problem is in Snowflake I am only having the float value, which is the first value I mentioned. Now I need to do a transformation to get to the date value. I got told that this is accomplished by using the CTCV_CONVERT_FLOAT_TO_DATE function in SAP ERP. Now I do not know how to accomplish this in Snowflake as there is not an equivalent function. Can someone explain how that function works to emulate it in some kind of way? Or point me to a recourse I could use.

Thank you very much.

I was not able to find any other information regarding this function.


Solution

  • So, I did some googling and this is what I came up with.

    0x is used in code to indicate that the number is being written in hex.

    suffix p24 indicates that the number is multiplied by 2^24.

    1.34b049 hex value in decimal format is 1.20581489801406860352.

    When 1.20581489801406860352 is multiplied with 2^24 (16777216), you get 20230217 which is the date you are looking for.

    I don't think the above calculation has anything to do with SAP function module CTCV_CONVERT_FLOAT_TO_DATE