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.
{"ft_lang_label":"__label__en","ft_lang_prob":0.5394124,"math_prob":0.95342916,"size":4823,"snap":"2019-26-2019-30","text_gpt3_token_len":1399,"char_repetition_ratio":0.22100021,"word_repetition_ratio":0.74771243,"special_character_ratio":0.31536388,"punctuation_ratio":0.06791569,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.97397333,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-07-24T04:42:10Z\",\"WARC-Record-ID\":\"<urn:uuid:8a89b33e-a148-4f95-b03b-ae35cfde8737>\",\"Content-Length\":\"154539\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:5329d6b3-bd9f-4490-bb61-d54d529fa295>\",\"WARC-Concurrent-To\":\"<urn:uuid:45dd3cfd-9a81-4464-a629-049497efc71b>\",\"WARC-IP-Address\":\"208.74.204.160\",\"WARC-Target-URI\":\"https://community.teradata.com/t5/Database/Number-of-Days-in-No-of-Year-months-and-days-format/td-p/78957\",\"WARC-Payload-Digest\":\"sha1:L3ZIJWXEAGCE4KPOLAOJLNHISZSZSIZF\",\"WARC-Block-Digest\":\"sha1:KFLAOII4KVA6NUR7HLM6W3HVQ7O4HTGG\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-30/CC-MAIN-2019-30_segments_1563195530385.82_warc_CC-MAIN-20190724041048-20190724063048-00337.warc.gz\"}"}