Integer to Date format?

Database
Fan

Integer to Date format?


I am new to Teradata. Now one of my work involves pulling data from Teradata and populat into Oracle tables. I have to pull data that falls between a particular date range.I have a table, where date is stored as YYYYMM Integer format ( ex 200607). How can I convert this into a date field like, say 01/07/2006 ?. I tried Date Format, cast functions, but nothing works.

Thanks in advance.
5 REPLIES
Enthusiast

Re: Integer to Date format?

This format looks more like a julian format rather than YYYYMM. Please check with the source system guys for the proper date format.

Thanks
SP
Fan

Re: Integer to Date format?

No, this is Integer format. I checked the documentation. They do have one Julin date column but the date is diiferent than I am looking for.

Thanks
Senior Apprentice

Re: Integer to Date format?

Hi Elango,
change it to the Teradata internal date format:

select (200607 - 190000) * 100 + 1 (date)

Dieter
Fan

Re: Integer to Date format?

Thnks,

I am trying some thing like this :
SELECT
(DATE_YYYYMM - 190000) * 100 + 1 (date),

where the DATE_YYYYMM is the column name, but it gives invalid date error?.
I think, I am missing some thing in the query?.
Senior Apprentice

Re: Integer to Date format?

Hi Elango,
there are probably some invalid values within DATE_YYYYMM:

select DATE_YYYYMM
from my_table
where DATE_YYYYMM mod 100 not between 1 and 12
or DATE_YYYYMM = 0

Dieter