STR_TO_DATE equivalent in snowflake

Question

Trying to replicate the below Mysql expression in snowflake but facing issues with the same , The same works perfectly for me in mysql.

SELECT STR_TO_DATE(CONCAT('201439',' Sunday'), '%X%V %W');

Please see below the sample query and the output the below works in Mysql .

SELECT STR_TO_DATE(CONCAT('201439',' Sunday'), '%X%V %W');
Output -- 2014-09-28

SELECT STR_TO_DATE(CONCAT('201440',' Sunday'), '%X%V %W');
output -- 2014-10-05

SELECT STR_TO_DATE(CONCAT('202313',' Sunday'), '%X%V %W');
output -- 2023-03-26

Where 202313 is the year and the Week no , Sunday is the Weekstart day and the returned output "2023-03-26" would be the week start date.

struggling to get the alternative for the same in snowflake.

Answer 1

There is no function for that by default, but you can create one of your own for this, like for example:

CREATE OR REPLACE FUNCTION beginning_of_the_week( IsoWeek VARCHAR )
RETURNS DATE
AS 'IFF(SUBSTR(IsoWeek,5)=1,TO_DATE(SUBSTR(IsoWeek,1,4),''YYYY''),
DATEADD(week,SUBSTR(IsoWeek,5)-2,DATEADD(day,1,LAST_DAY(TO_DATE(SUBSTR( IsoWeek,1,4),''YYYY''),week))))';

When calling it:

SELECT mydate, beginning_of_the_week( mydate ) as bdate, WEEKISO( bdate ) as verification
FROM VALUES ('20191'), ('20192'), ('201912'),('201951') t (mydate);

+--------+------------+--------------+
| MYDATE |   BDATE    | VERIFICATION |
+--------+------------+--------------+
|  20191 | 2019-01-01 |            1 |
|  20192 | 2019-01-07 |            2 |
| 201912 | 2019-03-18 |           12 |
| 201951 | 2019-12-16 |           51 |
+--------+------------+--------------+

See more information here.