Teradata - Date format conversion from INT(yyyymmdd) type to date(mm/dd/yyyy) in Teradata SQL Assistant

Question

I have dates in integer format in a column. The length is 11. Example values current format integer (11) --> date format required

• yyyymmdd --> dd/mm/yyyy
• 20121203 --> 03/12/2012
• 20090403 --> 03/04/2009

Can someone suggest a solution keeping in mind that the change need to reflect across the entire column in the table?

Answer 1

Use STR_TO_DATE :

select STR_TO_DATE(col1, "%Y%m%d") as my_date
from test_tbl;

Result:

my_date
2012-12-03
2009-04-03

Demo

Or DATE_FORMAT as previous answer:

select DATE_FORMAT(col1, "%d/%m/%Y") as my_date
from test_tbl;

Result:

my_date
03/12/2012
03/04/2009

Demo

Maybe using both:

select DATE_FORMAT(STR_TO_DATE(col1, "%Y%m%d"),'%d/%m/%Y') as my_date
from test_tbl;

my_date
03/12/2012
03/04/2009

Demo

Answer 2

The easiest way is based on Teradata's storage of dates:

Cast(intdate - 19000000 AS DATE) 

Of course this only works if there are no bad dates, as your source seems to be MySQL there might be 20220200, etc.

Answer 3

For Teradata SQL Assistant, you can use TO_CHAR and then CAST it to a DATE format like this:

SEL CAST(TO_CHAR(20211015) AS DATE FORMAT 'YYYYMMDD')

Result: 10/15/2021 (mm/dd/yyyy)

Answer 4

Docummentation: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

DATE_FORMAT("20121203", "%d/%m/%Y")