How to convert integer to a date format

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

How to convert integer to a date format

Hi Guys,

 

I have the below issue:

Column in table should be a date, but show as :

2,017,331

2,016,326

2,017,330

 

I want to convert these so they look more like a date i.e.

2017-03-31

2016-03-26

2017-03-30

 

I have tried:

sel cast (column_x AS DATE Format 'YYYYMMDD')

from table_1

 

but doesn't seem to work. - Any help Appreciated.

Thanks


Accepted Solutions
Junior Contributor

Re: How to convert integer to a date format

Simply use the code I posted:

To_Date(trim(columnname), 'yyyyddd')
1 ACCEPTED SOLUTION
8 REPLIES
Junior Contributor

Re: How to convert integer to a date format

Is this your actual data, YYYYM[M]DD? Ouch.

2017331 -> 2017-03-31

20171231 -> 2017-12-31

 

Hopefully not YYYYM[M]D[D]

201733 -> 2017-03-03?

2017111 -> 2017-????

Senior Apprentice

Re: How to convert integer to a date format

Hi,

The examples that you've provided are invalid dates.

 

Instead of 2017331 do you mean 20170331?

 

If so then simply use:

SELECT CAST(column-name-19000000 AS DATE) 
FROM table-name;

This will change data type to a DATE, then you can format it as required.

 

If the data value that you've provided is what is stored then you have to manipulate it a bit. The following has the logic which you should be able to simplify:

SELECT  column-name MOD 1000 AS c2
   ,10 * (col1 - c2) AS c3
   ,CAST( ((c3+c2) - 19000000) AS DATE) AS date_col1
FROM table-name;

HTH
Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: How to convert integer to a date format

Hey Dave,

 

The later is the correct value i see. (2,017,331).

***EDIT: - I have a feeling this is worse than i anticipated - i can see data with 2,016,342 - wouldnt even know what that is even implying :| !

 

However, after playing around and tweaking the suggested logic, the outcome i get:

 

SELECT Failed. 2666: Invalid date supplied for table_name.column_name

 

Cheers,

K45

Junior Contributor

Re: How to convert integer to a date format

Check max(columnname mod 1000), when it's 365 or 366 you got the year plus the day of the year:

To_Date(trim(columnname), 'yyyyddd')

 

 

 

Re: How to convert integer to a date format


@dnoethwrote:

Check max(columnname mod 1000), when it's 365 or 366 you got the year plus the day of the year:

To_Date(trim(columnname), 'yyyyddd')

 

 

 


Hey Dnoeth,

 

Thanks for the replies.

 

Checking MAX(column_name mod 1000) = 331

 

Cheers,

K45

Junior Contributor

Re: How to convert integer to a date format

Then it's probably data from 2017 up to Nov. 27th :-)

Re: How to convert integer to a date format

I'm assuming i would not be able to convert any of the data to a DATE then?

Junior Contributor

Re: How to convert integer to a date format

Simply use the code I posted:

To_Date(trim(columnname), 'yyyyddd')