0

I have a table in which dates are stored in string format and I want to change its format from yyyyMMdd to yyyy-MM-dd and store it back in string format only. I am using Aginity workbench.

Referred link - convert MM/DD/YYYY to YYYYMMDD in redshift , and its solution is not working properly.

First,I tried it in simple query, which is working fine :

select to_char(to_date('20150614', 'yyyyMMdd'),'yyyy-MM-dd') as SDate;

Output :

sdate 
2015-06-14

However, when I am trying in my actual query(in JOIN), Its giving me some weird value :

select to_char(to_date(s.date, 'yyyy-MM-dd'),'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

Output :

sdate           date          code  id  
139496-03-09    20150614      art   art

I am not sure, if this is because of query on bulk data or using JOIN or any other issue.

Can someone please help me on this or is there any other solution, I am new to RedShift. Any help would appreciated !!!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user036
  • 13
  • 4
  • The conversions are not the same in the two queries. – Gordon Linoff Aug 19 '19 at 14:05
  • Could you please explain more on this? I didn't get your comment. I am passing string only on s.date value- to_char(to_date(s.date, 'yyyy-MM-dd'),'yyyy-MM-dd') – user036 Aug 19 '19 at 14:09
  • In your first query you specify date format `yyyyMMdd` for your `to_date()` function and it works. In your second query you specify date format `yyyy-MM-dd` and it fails. What does `s.date` actually look like? I suspect you merely need to make your second query look just like your first with `to_char(to_date(s.date, 'yyyyMMdd'), 'yyyy-MM-dd') as SDate` – JNevill Aug 19 '19 at 14:13
  • Thanks Gordon Linoff and JNevill for your reply. Now closing this question. – user036 Aug 19 '19 at 15:11

0 Answers0