How to Covert date in the JSON data

[arvindnsn]

Hello,

I am trying to work with the date (timestamp to be precise) and getting into trouble. My requirement is below.

I have a stage table where the data is stored as JSON in a Variant Column and the data looks like below in that column.

{
“message_body”: {
“campus_code”: “TEST”,
“campus_name”: “TEST”,
“event_type”: “TEST”,
“location_code”: “A00000”,
“location_name”: “TEST”,
“order”: {
“credit_total”: 0,
“app_version”: “1.0.9”,
“asap”: 1,
“order_datetime”: “2021-01-08 18:19:34”
}
“timezone_offset_minutes”: -360,
}
}
I have below requirements.

1. Convert the Datetime into only date, so i tried the below and its failing

select TO_TIMESTAMP((body:message_body:order:order_datetime), ‘yyyy-mm-dd HH24:MI:SS’) FROM “stage_table”. its failing with below error message

SQL compilation error: error line 1 at position 7 too many arguments for function [TO_TIMESTAMP(GET(GET(GET(stage_table.BODY, ‘message_body’), ‘order’), ‘order_datetime’), ‘yyyy-mm-dd HH24:MI:SS’)] expected 1, got 2

2. I have to subtract the minutes from order_datetime. I have to subtract timezone_offset_minutes from
   order_datetime

select datetime, dateadd(minute, -300, body:message_body:order:complete_datetime) FROM
“stage_table”. its failing with below error message

Timestamp ‘’ is not recognized

Any quick help is hugely appreciated.

Thanks
Aravind N

[ptaylor]

I suggest not trying to do this in SQL. Just get the datetime as a string, and use a Mapper expression to do the date manipulation using the SnapLogic expression language.