SQL Server到雪花查询转换

Question

我正在尝试将下面的查询转换为雪花，但是我想出的是，我的错误使我无法将'04/17/22'转换为数字值。

SQL：

  SELECT  
  user_id AS u_id,  
  Substring(Max( CONVERT(VARCHAR(10), system_modstamp, 121) +   
   CASE -- Categorizing all of the team roles  
   WHEN team_member_role LIKE 'AM%'   
    OR team_member_role LIKE '%AM %'   
    OR team_member_role LIKE 'ASR%'   
   THEN 'AM Sales'   
   WHEN team_member_role LIKE '%fsr%'   
   THEN 'FSR'   
   WHEN team_member_role LIKE '%RSD%'   
    AND team_member_role NOT LIKE '%parts%'   
   THEN 'AC Sales'   
   WHEN team_member_role LIKE 'RSA%'   
   THEN 'AC Sales'   
   ELSE team_member_role   
  END  
  ), 11, 99) AS team_groups, Max(system_modstamp) AS SYSTEM_MODSTAMP   
 FROM S_SFDC_ACCOUNT_TEAM   
 GROUP BY user_id

雪花：

SELECT  
  user_id AS u_id,  
  SUBSTR(Max( TO_VARCHAR( system_modstamp,'YYYY-MM-DD') + 
CASE WHEN team_member_role LIKE 'AM%' OR team_member_role LIKE '%AM %' OR team_member_role LIKE 'ASR%' THEN 'AM Sales' WHEN team_member_role LIKE '%fsr%' THEN 'FSR' WHEN team_member_role LIKE '%RSD%' AND team_member_role NOT LIKE '%parts%' THEN 'AC Sales' WHEN team_member_role LIKE 'RSA%' THEN 'AC Sales' ELSE team_member_role END 
), 11, 99) AS team_groups, Max(system_modstamp) AS SYSTEM_MODSTAMP 
FROM S_SFDC_ACCOUNT_TEAM   
GROUP BY user_id

Answer 1

为了关闭，如注释中所示：

• 问题是使用+用于字符串串联的，因为在雪花中，您需要使用||而不是使用+。

错误“无法将'...'转换为数字值”表明它试图将该日期转换为（将日期转换为字符串），然后Snowflake试图将其转换为+操作。

谢谢Pankaj和Mike！