Integer(7) to Date

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-01

sel 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-01

sel 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;                          

        }