0

I have a date column that is of a string format. The dates appear like,

DDMMYYY or DDMYYYY 04032021 0412021

It has been a nightmare trying to split them and add '-' because they aren't all the same length. Not like a standard format of DDMMYYYY. I have also tried to split them by position. I have tried to cast it(the column) as a date.

Do you all have any suggestions for a way to get the entire column properly formatted and displaying as a date column? I need to filter on it.

SunManULTO
  • 1
  • Whoever is responsible for storing date values in a `varchar` column should be forced to fix your problem. –  May 17 '22 at 06:56

1 Answers1

0

It seems that the month and the year are on a fixed position relative to the start resp. end of the string.

So a trivial solution using substring would extract them and interpret the rest (padded with zeroes to the length of two) as the month.

select  
  dt,
  substring(dt,1,2) dd,
  LPAD(substring(dt,3,length(dt)-6),2,'0') mm,
  substring(dt,length(dt)-3) yyyy
from T;

dt      |dd|mm|yyyy|
--------+--+--+----+
04032021|04|03|2021|
0412021 |04|01|2021|

Conversion to date data type is a simple concatenation and a call of to_date

select  
  dt,
  to_date(
    substring(dt,1,2)|| -- dd
    LPAD(substring(dt,3,length(dt)-6),2,'0')|| -- mm
    substring(dt,length(dt)-3) -- yyyy
   ,'DDMMYYYY' ) ddt 
from T;

dt      |ddt       |
--------+----------+
04032021|2021-03-04|
0412021 |2021-01-04|
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • The first example in the question only has a 3 digit year. Btw: `substring(dt,length(dt)-3)` can be simplified to `right(dt, 4)` –  May 17 '22 at 07:00