Skip to content
Advertisement

concatenate date + time to make timestamp

I am using dbt and snowflake to parse a json.. Currently, I parse two cols, date and time separately. Now, I want to concatenate both and assign the type timestampto them

SELECT 
    JSON_DATA:"Required_Collect_Time_From"::time as "REQUIRED_COLLECT_TIME_FROM",
    JSON_DATA:"Required_Collect_Date"::time as "REQUIRED_COLLECT_DATE",
FROM {{ source('INGEST_DATA', 'LOAD' ) }}

I tried this::

    timestamp_ntz_from_parts(JSON_DATA:"Required_Collect_Date"::date,  JSON_DATA:"Required_Collect_Time_From":time) as "REQUIRED_TIMESTAMP",

However, the col “REQUIRED_TIMESTAMP” is just always empty in my final table. What else can I try?

Advertisement

Answer

I assume JSON_DATA:”Required_Collect_Time_From”:time has a typo in here, as it should be JSON_DATA:”Required_Collect_Time_From”::time.

Both these variations works for me:

select timestamp_ntz_from_parts(to_date('2013-04-05'), to_time('12:00:00'));
select timestamp_ntz_from_parts('2013-04-05'::date, '12:00:00'::time);
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement