我正在运行一个查询,说明Redshift在“ SQL错误[500310] [57014]:Amazon无效的操作:将文本转换为日期时出错;”中引发错误。
但是,当我删除Joined表或从WHERE子句中删除转换时,它不再引发错误。因此,我怀疑正在发生其他情况,并且此错误会引起误解。
我查看了数据,没有一个日期看起来很奇怪或超出范围。
以下是引发错误的查询:
SELECT m.member_id,t.tracker_id,DATE(ma.yyyymmdd) AS tracked_date,DATE(ma.updated_date) AS updated_date
FROM import_genesis.member_activities ma
INNER JOIN master."member" m
ON ma.memberid = m.member_legacy_id
INNER JOIN master.action_activity aa
ON ma.activity_type = aa.activity_type
INNER JOIN master.tracker t
ON t.action_id = aa.action_id
WHERE DATE(ma.yyyymmdd) > DATE(DATEADD(DAY,-14,GETDATE()))
AND DATE(ma.yyyymmdd) <= GETDATE();
以下两个不会引发错误的查询:
SELECT DATE(ma.yyyymmdd) AS tracked_date,DATE(ma.updated_date) AS updated_date
FROM import_genesis.member_activities ma
WHERE DATE(ma.yyyymmdd) > DATE(DATEADD(DAY,GETDATE()))
AND DATE(ma.yyyymmdd) <= GETDATE();
SELECT m.member_id,DATE(ma.updated_date) AS updated_date
FROM import_genesis.member_activities ma
INNER JOIN master."member" m
ON ma.memberid = m.member_legacy_id
INNER JOIN master.action_activity aa
ON ma.activity_type = aa.activity_type
INNER JOIN master.tracker t
ON t.action_id = aa.action_id;
关于什么是错的或任何潜在的解决方法的任何想法?