CODEWITHSUNDEEP
sqlteradatateradata-sql-assistant
evdo_d
evdo_d

Reputation: 27

How to calculate exact number of years between two dates

I have two columns, Birth_Date and Publish_Date. I need to calculate number of years (taking into account months) between the Publish_Date and Birth_Date. (DATE, FORMAT'YYYYMMDD')

I have attempted to use DATEDIFF, however it does not seem to be picking this up. Solution from here: How to calculate age (in years) based on Date of Birth and getDate()

,  CASE WHEN dateadd(year, datediff (year, Brth_Dt, Pub_Dt), Brth_Dt) > Pub_Dt
        THEN datediff(year, Brth_Dt, Pub_Dt) - 1
        ELSE datediff(year, Brth_Dt, Pub_Dt)
   END as Brth_Yrs

Error: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'year' keyword.

Also tried this solution from How to calculate Age/Number of Years between two dates


  ,    DATEDIFF(YEAR, Brth_Dt, Pub_dt) + 
                         CASE 
                           WHEN MONTH(@Pub_dt) < MONTH(Brth_Dt) THEN -1 
                           WHEN MONTH(@Pub_dt) > MONTH(Brth_Dt) THEN 0 
                           ELSE 
                             CASE WHEN DAY(@Pub_dt) < DAY(Brth_Dt) THEN -1 ELSE 0 END 
                         END)    As Brth_Yrs

Error: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'YEAR' keyword.

Can anyone help on how to write this?

Upvotes: 0

Views: 3479

Answers (2)

tinazmu
tinazmu

Reputation: 5139

Teradata does not support DATEDIFF, DATEADD functions.

Here is one way of calculating age to current date. We turn the dates into integers in YYYYMMDD format using Teradata's EXTRACT function to extract date parts; we then simply subtract DOB from the current date.

You can just substitute CURRENT_DATE with your Publish_Date and DOB with Birth_Date.

 ((EXTRACT(YEAR FROM CURRENT_DATE)*10000+
   EXTRACT(MONTH FROM CURRENT_DATE)*100+
   EXTRACT(DAY FROM CURRENT_DATE)) -
  (EXTRACT(YEAR FROM DOB)*10000+
   EXTRACT(MONTH FROM DOB)*100+
   EXTRACT(DAY FROM DOB))
 )/10000 as Age

Upvotes: 0

Sathiya
Sathiya

Reputation: 239

Try the below SQL QUERY for finding Year difference between two dates using DATEDIFF function.

SELECT DATEDIFF(year, Brth_Dt, Pub_Dt) AS date_difference FROM Employee;

Upvotes: 0

Related Questions