Is there a way to pull just the Year out a VARCHAR datetime value?

I am working on a project, in Snowflake, that requires me to combine pest & weather data tables, but the opposing tables do not share a common column. My solution has been to create a view that extracts the year from the Pest Table dates, format ex. CREATION_DATE: 03/26/2020 09:11:15 PM, to match the YEAR column in the Weather tables, format ex. DATEYEAR: 2021. However, I have come to find that the dates in the pest report are VARCHAR as opposed to traditional date/datetime values. Is there a way to pull just the Year out the VARCHAR date value? Additional information: I cannot change the tables themselves, I will need to create a view that preserves all other columns and adds a new "DATEYEAR" column.

Solution 1:

Yes , we can and below is working example:

   create table test (dt string ); 
insert into test(dt) values ('01/04/2022'); 
Select dt, DATE_PART( year, dt::date) from test

Solution 2:

To make it easy, you can split the string into an array and take the third member of the array (using 2 since arrays are 0 based):

select strtok_to_array('03/26/2020', '/')[2]::int as MY_YEAR;