Convert Integer to date

Database
Enthusiast

Convert Integer to date

I have a Org_date which is an Integer, I wanted to change to a date format. There is also a -1 value in this field.

 

The values in Org_date are like.

20180420

20171207

-1

 

I tried doing trycast, to_date(trim(org_dt),'yyyymmdd') nothing seemed to work. Please can someone help?


Accepted Solutions
Teradata Employee

Re: Convert Integer to date

Use a CASE statement such as

CASE WHEN ORG_DATE > 0 THEN CAST(ORG_DATE - 19000000 AS DATE) END /* relies on old Teradata INTEGERDATE */

or

 

CASE WHEN ORG_DATE > 0 THEN CAST(CAST(ORG_DATE AS VARCHAR(8)) AS DATE FORMAT 'YYYYMMDD') END

or even

 

CASE WHEN ORG_DATE > 0 THEN TO_DATE(TRIM(ORG_DATE),'yyyymmdd') END
1 ACCEPTED SOLUTION
2 REPLIES 2
Teradata Employee

Re: Convert Integer to date

Use a CASE statement such as

CASE WHEN ORG_DATE > 0 THEN CAST(ORG_DATE - 19000000 AS DATE) END /* relies on old Teradata INTEGERDATE */

or

 

CASE WHEN ORG_DATE > 0 THEN CAST(CAST(ORG_DATE AS VARCHAR(8)) AS DATE FORMAT 'YYYYMMDD') END

or even

 

CASE WHEN ORG_DATE > 0 THEN TO_DATE(TRIM(ORG_DATE),'yyyymmdd') END
Highlighted
Enthusiast

Re: Convert Integer to date

Thank you! It worked