Issue with DateTime fields
rikat5tran opened this issue · 2 comments
IN the cortex framework it is expected that dates have the date format with SAP. I like to request that in the config you can format how the date fields and time fields are handled in Cortex.
My suggestion is for dates the following options.
- date
- datetime
- string + format [yyyymmdd]
For time fields
- time
- string + format [HHMISS]
I suggest that there is a possibility that you can use also the string data type or date time data type in the solution.
The following functions are affected.
- fiscal_case1.sql
- fiscal_case2.sql
- fiscal_case3.sql
- currency_conversion.sql (function gdatu_to_date)
Further for the time fields used in the ordertocash reporting should also support string values.
- OrderToCash.sql
here is an example:
/*OrderCycleTimeInDays*/ IF(Deliveries.ActualGoodsMovementDate_WADAT_IST IS NOT NULL, TIMESTAMP_DIFF(CAST(CONCAT(CAST(Deliveries.Date__proofOfDelivery___PODAT AS DATE), ' ', CAST(Deliveries.ConfirmationTime_POTIM AS TIME FORMAT 'HH24MISS') ) AS TIMESTAMP), CAST(CONCAT(CAST(SalesOrders.CreationDate_ERDAT AS DATE), ' ', CAST(SalesOrders.CreationTime_ERZET AS TIME FORMAT 'HH24MISS')) AS TIMESTAMP), DAY), NULL) AS OrderCycleTimeInDays,
This can be solved with the jinja script. Makes the integration with Fivetran's LDP solution much smoother.
If you want me to discuss happy to setup a meeting.
HI @rikat5tran , thanks for reaching out with a suggestion. The data foundation expects data landed and converted as in here: https://cloud.google.com/solutions/sap/docs/bq-connector/latest/planning#data_type_mapping
Because we cater to multiple integration solutions for SAP to BQ, and this specific request would have a massive impact in deployment, data handling and reporting downstream, I'll reject the suggestion and recommend you reach out to the Fivetran team to discuss transformations specific to this tool so the change is sustainable and compatible with the formats expected by the data foundation.