RedShift中to_char(to_date)函数在JOIN查询中格式转换异常求助

Let's break down what's going wrong here and how to fix it quickly:

The Root Cause #

In your JOIN query, you're using the wrong format mask for to_date()!

Your s.date column stores dates as yyyyMMdd strings (like 20150614), but you're telling RedShift to parse it as yyyy-MM-dd:

to_date(s.date, 'yyyy-MM-dd')

This misalignment causes RedShift to misinterpret the string, leading to that nonsensical 139496-03-09 date value. Your single-test query was correct because you used the right mask (yyyyMMdd), but you didn't carry that over to the JOIN.

Fixed Query #

Just update the format mask in your JOIN query to match the actual storage format of s.date:

select 
  to_char(to_date(s.date, 'yyyyMMdd'),'yyyy-MM-dd') as SDate, 
  s.date, 
  e.code, 
  s.id 
from student as s 
join event as e on e.code >= s.id 
order by SDate asc 
limit 1

This should return the correct 2015-06-14 for SDate as expected.

Alternative: String Manipulation (No Date Conversion) #

If you want to avoid date parsing entirely (which can be more efficient for fixed-length date strings), you can use string concatenation instead:

select 
  left(s.date, 4) || '-' || substring(s.date, 5, 2) || '-' || right(s.date, 2) as SDate,
  s.date,
  e.code,
  s.id
from student as s
join event as e on e.code >= s.id
order by SDate asc
limit 1

This works because your input is always 8 characters long:

• left(s.date,4) grabs the year (2015)
• substring(s.date,5,2) grabs the month (06)
• right(s.date,2) grabs the day (14)
• We just add the - separators to get the desired format.

Why the Weird Date Happened? #

When RedShift gets a string that doesn't match the specified format mask, it falls back to "lenient parsing" which can produce unexpected results. In your case, trying to parse 20150614 as yyyy-MM-dd confuses the parser—it can't find the - separators, so it tries to interpret the string in unintended ways, leading to that absurdly large year value.

内容的提问来源于stack exchange，提问作者user036