0

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?

evdo_d
  • 27
  • 1
  • 7
  • If the embedded services system functions have been enabled for your instance of Teradata, the following information should provide you the information you need after some rounding - https://docs.teradata.com/r/kmuOwjp1zEYg98JsB8fu_A/YefvyMadaWAKz78zm2L0mA - This doc may also help, although it's not clear if you can implement the functionality over entire columns - https://docs.teradata.com/r/1DcoER_KpnGTfgPinRAFUw/tdTWT16GAlOSMnzvflzShw - otherwise, in order to get months out of data in Teradata you could look into the Extract function – procopypaster Dec 09 '21 at 06:09
  • You're getting those errors because the dateadd and datediff functions are not supported in Teradata. – procopypaster Dec 09 '21 at 06:12
  • As alluded to by @procopypaster use `MONTHS_BETWEEN(...)/12` perhaps with `TRUNC` or `CEILING` or `ROUND` if "exact number of years" means an integer. And if you need to adjust +/- one year it's better to use `ADD_MONTHS` to add or subtract 12 months (due to leap year complications). – Fred Dec 09 '21 at 18:28
  • Some ideas [here](https://stackoverflow.com/q/57810365/4003419)? – LukStorms Dec 10 '21 at 06:10
  • What is the "exact number of years" for a user born on Feb 29 on Feb 28 in a non-leap year? – dnoeth Dec 10 '21 at 07:55

2 Answers2

0

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;

Sathiya
  • 250
  • 2
  • 2
0

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
tinazmu
  • 3,880
  • 2
  • 7
  • 20