Database

## 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

## 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 3

## 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.

## 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'); `

## 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.