Citibike data date format

Question

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 tried all above query can someone let me me know if csv file thats loaded in snowflake with data type as VARCHAR how to query it in snowflake table.

2. 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

Answer 1

Is the 0.00 always a 0.00? If so:

select to_timestamp('6/1/2013 0.00','MM/DD/YYYY 0.00');