AGE between 2 dates in snowflake

Question

In Netezza we have function called as AGE but snowflake i couldn't find anything similar

Netezza: select AGE('2019-12-01', '2018-12-12') - o/p: 11 mons 20 days

Is there similar function in snowflake?

Found some workaround to get age between two dates SELECT DATEDIFF(dd,'2018-12-01', '2019-12-12') ||' days' -- o/p: 376 days — Jeyavel, Dec 24 '19 at 03:18

score 5 · Accepted Answer · answered Dec 24 '19 at 04:01

So given Netezza is based on PostgreSQL, so it's AGE function, is equivalent to Snowflake's DATEDIFF, but like PostgreSQL interval it defaults to expressing it in simplified terms of Years/Months/Days. Snowflake doesn't offer a function that does that. Also the order of the parameters is swapped.

Thus your AGE('2019-12-01', '2018-12-12') would be DATEDIFF('days', '2018-12-12', '2019-12-01')

I was going to suggest you could do the following to build up the parts, but I am quite sure this is a bug in snowflake:

select '2018-12-12'::date AS st
,'2019-12-01'::date AS et
--,DATEDIFF('month', st,et) AS t_m
--,DATEDIFF('days', st,et) AS t_d
,DATEDIFF('year', st,et) AS y
,DATEADD('year', y, st) AS yst
,DATEDIFF('month', yst,et) AS m 
,DATEADD('month', m, yst) AS mst
,DATEDIFF('day', mst,et) AS d 
;

AS y is 1 and t_m is 12 which I don't believe they should be as it's only 354 days..

I will open a bug report. But if/when that's fixed then you can compare the y, m, & d together to get the string back if you needs want that.. otherwise I would stick to days myself..

For anybody else who comes across this, the datediff behavior described here is not a bug. It is following snowflake's documentation. When using datediff to calculate a year, it only looks at the year. Thus `select DATEDIFF('year', '2020-12-31', '2021-01-01')` returns 1 because there's 1 year difference between 2020 and 2021, even though there's only actually 1 day between these 2 dates. — Darinth, Apr 20 '23 at 20:22
They documented is "after the fact" to avoid changing it. So yes "it's is not a bug because they wrote the words, THIS IS NOT A BUG", and I am a fan-boy, but it still is not what people expect out of the function. — Simeon Pilgrim, Apr 20 '23 at 22:38

AGE between 2 dates in snowflake

1 Answers1