Number of Days in No of Year months and days format

Database
Teradata Employee

Number of Days in No of Year months and days format

If I subtract two dates in Teradata I get 

SELECT DATE '2018-01-15' - DATE '2017-01-01'

Result =  379 days

I want it in No of Years No of Months No of Days format

Example = 379 days should translate to 10014

which is 1 year 00 months 14 days (1 00 14 )

how to achieve this in Teradata , is there a inbuild functions or a specific CAST I can use ?

Tags (3)

Accepted Solutions
Junior Contributor

Re: Number of Days in No of Year months and days format

There's no built-in function/format, this is a proprietary DB2 calculation.

 

And then I remembered I've done this a long time ago :-)

REPLACE FUNCTION date_duration(date1 DATE, date2 DATE)
RETURNS INTEGER
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
CASE
  WHEN date1 >= date2
  THEN
     (((Cast(Months_Between(date1,Trunc(date2,'mon')) AS INT)) - CASE WHEN Extract(DAY From date2) <= Extract(DAY From date1) THEN 0 ELSE 1 END)  /  12) * 10000 +                    -- years
     (((Cast(Months_Between(date1,Trunc(date2,'mon')) AS INT)) - CASE WHEN Extract(DAY From date2) <= Extract(DAY From date1) THEN 0 ELSE 1 END) MOD 12) * 100   +                    -- months
            (Extract(DAY From date1) - Extract(DAY From date2) + CASE WHEN Extract(DAY From date2) <= Extract(DAY From date1) THEN 0 ELSE  Extract(DAY From Last_Day(date2)) END)    -- days
  ELSE
    -((((Cast(Months_Between(date2,Trunc(date1,'mon')) AS INT)) - CASE WHEN Extract(DAY From date1) <= Extract(DAY From date2) THEN 0 ELSE 1 END)  /  12) * 10000 +                   -- years
     (((Cast(Months_Between(date2,Trunc(date1,'mon')) AS INT)) - CASE WHEN Extract(DAY From date1) <= Extract(DAY From date2) THEN 0 ELSE 1 END) MOD 12) * 100   +                    -- months
            (Extract(DAY From date2) - Extract(DAY From date1) + CASE WHEN Extract(DAY From date1) <= Extract(DAY From date2) THEN 0 ELSE  Extract(DAY From Last_Day(date1)) END)) -- days
END

I hope this is the correct version.

And if you want to add/subtract such a duration:

REPLACE FUNCTION add_date_duration(date1 DATE, duration INT)
RETURNS DATE
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
CASE
  WHEN duration >= 0
  THEN Add_Months(Add_Months(date1, (duration / 10000)*12), (duration / 100 MOD 100)) + (duration MOD 100)
  ELSE Add_Months(Add_Months(date1 + (duration MOD 100), (duration / 100 MOD 100)), duration / 10000*12)
END

Of course it would be much better to implement the logic in a C-UDF.

 

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Number of Days in No of Year months and days format

Of course this would be possible, but why do you need that?

 

Standard SQL implements only year-month or day-second intervals for a good reason:

One month might be between 28 and 31 days, without knowing the actual start or end date it's useless.

 

Teradata Employee

Re: Number of Days in No of Year months and days format

You are right @dnoeth , But In DB2 if you subtract two dates without enclosing it in Days() function it will return the output in that format : Year Month Days , Looking to generate similar output using Teradata. 

So wanted to know if we do have any functions .

We do have ways to extarct no of months, but that alone doesn't serve my purpose. 

SELECT MONTHS_BETWEEN(DATE'1995-02-02', DATE'1995-01-01'); 
Junior Contributor

Re: Number of Days in No of Year months and days format

There's no built-in function/format, this is a proprietary DB2 calculation.

 

And then I remembered I've done this a long time ago :-)

REPLACE FUNCTION date_duration(date1 DATE, date2 DATE)
RETURNS INTEGER
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
CASE
  WHEN date1 >= date2
  THEN
     (((Cast(Months_Between(date1,Trunc(date2,'mon')) AS INT)) - CASE WHEN Extract(DAY From date2) <= Extract(DAY From date1) THEN 0 ELSE 1 END)  /  12) * 10000 +                    -- years
     (((Cast(Months_Between(date1,Trunc(date2,'mon')) AS INT)) - CASE WHEN Extract(DAY From date2) <= Extract(DAY From date1) THEN 0 ELSE 1 END) MOD 12) * 100   +                    -- months
            (Extract(DAY From date1) - Extract(DAY From date2) + CASE WHEN Extract(DAY From date2) <= Extract(DAY From date1) THEN 0 ELSE  Extract(DAY From Last_Day(date2)) END)    -- days
  ELSE
    -((((Cast(Months_Between(date2,Trunc(date1,'mon')) AS INT)) - CASE WHEN Extract(DAY From date1) <= Extract(DAY From date2) THEN 0 ELSE 1 END)  /  12) * 10000 +                   -- years
     (((Cast(Months_Between(date2,Trunc(date1,'mon')) AS INT)) - CASE WHEN Extract(DAY From date1) <= Extract(DAY From date2) THEN 0 ELSE 1 END) MOD 12) * 100   +                    -- months
            (Extract(DAY From date2) - Extract(DAY From date1) + CASE WHEN Extract(DAY From date1) <= Extract(DAY From date2) THEN 0 ELSE  Extract(DAY From Last_Day(date1)) END)) -- days
END

I hope this is the correct version.

And if you want to add/subtract such a duration:

REPLACE FUNCTION add_date_duration(date1 DATE, duration INT)
RETURNS DATE
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
CASE
  WHEN duration >= 0
  THEN Add_Months(Add_Months(date1, (duration / 10000)*12), (duration / 100 MOD 100)) + (duration MOD 100)
  ELSE Add_Months(Add_Months(date1 + (duration MOD 100), (duration / 100 MOD 100)), duration / 10000*12)
END

Of course it would be much better to implement the logic in a C-UDF.