I am using Citibike data and load it to snowflake table but as data is loaded i was having issues in loading online data i loaded it into a varchar data type. So its from CSV file and date is loaded in varchar field. Initially i was trying to see extract data in snowflake and got error that DATE trun function is not recognized. so i am trying to use multiple ways so that it can recognize the date field and if i will change the data type to date format ( timestamp_ntz) its not loading the data. this is the column that is varchar datatype and value is like this 6/1/2013 0:00
--this below not working date_trunc showing error select date_trunc( hour , starttime)as "date", Count(*) as "num_trips", avg(tripduration)/60 as "avg duration (mins)", avg(haversine(start_station_latitude, start_station_longitude, end_station_latitude, end_station_longitude)) as "avg distance (KM)" from trips2 group by 1 order by 1; Can t parse starttime as date with format DD/MM/YYYY
--this is not working as couldn t parse
SELECT TO_TIMESTAMP(TO_DATE(STARTTIME, MM/DD/YYYY )|| || TO_TIME(starttime, HH:MI ), YYYY-MM-DD HH24:MI )AS TIMESTAMP_DATE FROM TRIPS2; SELECT STARTTIME AS START_DATE_ORIG, to_varchar(TO_DATE(STARTTIME, ), YYYY-MM-DD ) AS START_DATE FROM TRIPS2; SELECT TO_VARCHAR(TO_DATE( 12/09/2023 , DD/MM/YYYY ), MM-DD-YYYY );
SELECT TO_VARCHAR(TO_DATE( starttime , DD/MM/YYYY ), MM-DD-YYYY ) from trips2;
i 对上述所有询问都进行审判,让我知道,在像VARCHAR这样数据类型的雪粉中装满的单壳如何在雪面板上盘问。
- If i am changing the datatype while loading the data by creating fileformat and chaning the datatype to date or timestamp_ntz its not letting me load the data. I have also tried saving the csv file in the date format than its not loading the data