How do I prioritize date format 'MM/DD/YYYY' over 'MM-DD-YYYY'

Question

How can I write a query in Teradata so that if the field Cont_Num has duplicate with different data formats('MM/DD/YYYY' and 'MM-DD-YYYY'), the query should only return the field with date format 'MM/DD/YYYY', but if there are no duplicates for Cont_Num and the date format present is only 'MM-DD-YYYY', it should return that field Example of the data i have is below

SELECT Cont_Num,FILL_DATE, 
FROM USS_LOAD_LOG_TABLES.Members 
WHERE Cont_Num IN ( 
1807369967,
1807453269,
1916796684,
2015276712,
2001306287
)
ORDER BY Cont_Num,FILL_DATE;

RESULT

Cont_Num        FILL_DATE
1807369967  11-13-2018
1807369967  11/13/2018
1807453269  11-18-2018
1807453269  11/18/2018
1916796684  06-11-2019
1916796684  6/11/2019
2001306287  01-08-2020
2001306287  1/8/2020
2015276712  03-28-2020

EXPECTED RESULT

RESULT

Cont_Num        FILL_DATE
1807369967  11/13/2018   
1807453269  11/18/2018
1916796684  6/11/2019
2001306287  1/8/2020
2015276712  03-28-2020

So your fill_date column is actually varchar(10) or something, not an actual date? — Andrew, Jul 08 '20 at 19:56
That's just horrible. Is there another column, some sort of identifier, that you can use to know how the date on that row is structured? — Andrew, Jul 08 '20 at 20:35
Unfortunately there is no other column that i can use an identifier — Bond, Jul 08 '20 at 20:50
I am trying to update a table, all the FILL_DATE in the target table are in the format 'MM/DD/YYYY', I tried using ROW_NUM as below but i'm not getting the result QUALIFY ROW_NUMBER() OVER (PARTITION BY Cont_Num ,PRES_ID ORDER BY Cont_Num ,PRES_ID DESC) = 1) — Bond, Jul 08 '20 at 21:00

score 0 · Answer 1 · answered Jul 08 '20 at 20:51

Of course, you should try to fix your data model, a date should be stored as DATE.

You can add a leading zero to a single digit day/month and remove the hyphen/dash to normalize the strings to a common format, which can be used in a ROW_NUMBER:

qualify
   row_number()
   over (partition by Cont_Num, RegExp_Replace(RegExp_Replace(FILL_DATE, '\b([\d])\b', '0\1'), '\D')
         order by case when FILL_DATE like '%/%' then 1 else 2 end) = 1

Hopefully only MMDDYYYY format is used.

Thanks alot , I have done as you've suggested and will try and give you a feedback. — Bond, Jul 08 '20 at 21:45

How do I prioritize date format 'MM/DD/YYYY' over 'MM-DD-YYYY'

1 Answers1