Converting the datediff function from sql server into a Teradta function

Database

Converting the datediff function from sql server into a Teradta function

Hello, I have the following function in sql server that I want to convert to Teradata Studio:

 

FLOOR(DATEDIFF(DAY "DOB", '28 FEB 2017')/365.25)) AS AGE

 

Thanks.

 

 

1 REPLY
N/A

Re: Converting the datediff function from sql server into a Teradta function

You can convert it like this

Cast((DATE '2017-02-28' - DOB)/365.250 AS INT) AS AGE

 

Caution, when you use 365.25 you get 17 as age (due to different rules for the precision of a decimal calculation).

 

In best case you can make a SQL UDF for this kind of recurring calculation.

 

I got an age calculation UDF which uses a slightly different approach (based on internal storage of a date):

REPLACE FUNCTION age(d1 DATE, d2 DATE)
RETURNS INT
SPECIFIC age_date
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
  ((d1 (INT)) - (d2 (INT))) / 10000