Converting data type inside OBJECT_CONSTRUCT in snowflake

I'm using Snowflake's Object_Construct to create a JSON array with values.

Example:

Select  OBJECT_CONSTRUCT(
        user_id,    user.id::TIMESTAMP,
        join_date,  user.create_date
       )
from user

Select object_construct(*) from above_table This is giving me the output as follows

|-------------------------------------------|
|              OBJECT_CONSTRUCT(*)          |
|-------------------------------------------|
| {                                         |
|   "user_id": 1,                           |
|   "join_date": "2021-06-18 19:51:19.000"  |
| }                                         |
|-------------------------------------------|

Can the join_date inside JSON be converted to timestamp data-type instead of STRING as the above?

JSON does not have a "date" data type, it has numbers, bool, string. Thus you ether need to store dates/timestamps as epoch seconds/milliseconds, or as formatted string.

SELECT 
    try_to_timestamp_ntz('2021-06-18 19:51:19.000') as t
    ,date_part('epoch_second', t) as epoch_s
    ,date_part('epoch_millisecond', t) as epoch_ms
    ,to_timestamp_ntz(epoch_s, 0) as time_from_sec    
    ,to_timestamp_ntz(epoch_ms, 3) as time_from_ms;

giving:

Ah so, you error on using the JSON will be that the values from a VARIANT/json object are VARIENT so to pass those to things like TO_TIMESTAMP they need casting to string

So here is building, cast, and fetching out values:

Select
    user.id
    ,user.create_date
    ,OBJECT_CONSTRUCT(
        'user_id',    user.id,
        'join_date',  user.create_date
       )::variant as json_data
   ,get(json_data, 'user_id') AS j_user_id_1
   ,json_data:user_id AS j_user_id_2
   ,get(json_data, 'join_date') AS j_user_create_date_1
   ,json_data:join_date AS j_user_create_date_2
   ,j_user_id_1::number as j_user_id_1_int
   ,j_user_create_date_2::timestamp as j_user_create_date_2_ts_a
   ,to_timestamp_ntz(j_user_create_date_2::string) as j_user_create_date_2_ts_b
   ,try_to_timestamp_ntz(j_user_create_date_2::string) as j_user_create_date_2_ts_c
FROM VALUES 
    (1, '2021-06-18 19:51:19.000' ) 
    AS user(id, create_date)
;