Database
Fan

## Integer(7) to Date

Hallo everyone

I have a record that has the date field defined as integer and the data in the field is like this

1870101

This equals 19870101, but when I try to cast it as an date value by the below way,

`sel cast(1870101 as date format 'yyyymmdd')--2087-01-01sel cast(870101 as date format 'yyyymmdd')--1987-01-01`

But when I try to cast dates of years above 2000

`sel cast(2091101 as date format 'yyyymmdd')--2109-11-01sel cast(091101 as date format 'yyyymmdd')--1909-11-01`

And I also tried in the below way,

`SELECT CAST(CAST(CAST(1870101 AS INTEGER FORMAT '999999') AS CHAR(6) ) AS DATE FORMAT 'YYYYMMDD')SELECT CAST((CAST (1870101 AS CHAR(7))) AS DATE FORMAT 'YYYYMMDD')`

Both the above methods returned Invalid Date error

Could you pls give me guide me to the correct way of casting the 7 length integer value to date

Thanks

Tags (1)
3 REPLIES
Enthusiast

## Re: Integer(7) to Date

Hi Marc,

Date format in TD is CYYMMDD. C here stands for century. For the year 1987, C = '0' and if C = 1 TD will assume it is 21st century.

If in the data that you have C = 1 stands for 20th century, I would suggest you subtract 1000000  from your input.

For example:

sel cast(1870101 - 1000000 as date format 'yyyymmdd') will give you 1987-01-01

Hope this helps!

PT

Fan

## Re: Integer(7) to Date

Thanks alot, this works just fine :)

Enthusiast

## Re: Integer(7) to Date

The reason why the date format is CYYMMDD  is because of the following formula ::

date =  (YEAR - 1900) * 10000 + (MONTH * 100) + DAY

When the "century" is less than (19) the number is negative.

Here is some "c" code to extract the date components::

if (Int_Date_Val > 0)

{

Year_Val = 1900 + Int_Date_Val/10000;

Month_Val = (Int_Date_Val%10000)/100;

Day_Val = Int_Date_Val%100;

}

else

{

IntDate1 = (1900+(Int_Date_Val/10000.0f))*10000;

Year_Val = IntDate1/10000;

Month_Val = (IntDate1%10000)/100;

Day_Val = IntDate1%100;

}