Adding integer number to integer date

General
Enthusiast

Adding integer number to integer date

need help to convert this SQL code to Teradata code

 

 

select * 
into #PHV
from     Bingo_table
where Date_ID between firstDate_ID and convert(int,convert(varchar(100),(CONVERT (datetime, CONVERT (char(8),firstDate_ID))+7),112))

 

Date_ID - integer date like 20170502

firstDate_ID - ineteger date like 20170502

2 REPLIES
Junior Contributor

Re: Adding integer number to integer date

Teradata stores dates internally as an integer using this formula:

(year-1900) * 10000 + month * 100 + day

which results in 1170502 for 2017-05-02.

 

Based on that you can add/subtract 19000000:

Cast(Cast(firstDate_ID-19000000 AS DATE) +7 AS INT) + 19000000

 

Would be much easier with a real Date, of course.

I never liked those integer dates anyway :-)

 

Enthusiast

Re: Adding integer number to integer date

hi, i'll check

 

this is  a database column type- it's not my choice:)

 

tnx x a lot