Converting date timestamp into date in snowflake

0
votes

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, “complete_datetime”: “2020-11-19 00:08:20” } “timezone_offset_minutes”: -360, } }

I have below requirement

Convert the Datetime into only Date, so i tried the 2 below queries

  1. select trunc(to_date(body:message_body:order:complete_datetime), 'DAY') FROM "stage_table";

  2. select date_trunc('DAY', to_date(body:message_body:order:complete_datetime)) FROM "stage_table";

The source date is "2020-11-19 00:08:20" and both the queries return the output as "2020-11-19 00:00:00" even though my expected value should be in the form of "YYYY-MM-DD".

Any help is highly appreciated

Thanks

Aravind N

1
dateformatsnowflake-cloud-data-platform
Can you show us a screenshot of where you see the date displayed like that? Both queries should work as expected – Felipe Hoffa
The session for was altered for the output datetime with a format other than "YYYY-MM-DD" and hence it was displaying the format with timestamp even after the conversion. I changed the session to have the default format as "YY-MM-DD" and now i see the conversion working properly. Thanks – Rajni
Does this answer your question? To convert date in Snowflake – Simon D
Yes. it resolved the issue. – Rajni

1 Answers

1
votes

I'm a big fan of using :: to cast values to other types. I was able to get this working with a similar nested JSON field in my own Snowflake instance. Here is my attempt to adapt for your use case:

select body:message_body:order:complete_datetime::date
from "stage_table"