Search code examples
sqldatedatedifftableau-desktop

Mention Date difference as Years, Months and Days - tableau

I want the datediff difference to be mentioned as for example 1 year, 2 months, and 5 days in Tableau desktop.

My Year and month works fine, i can't seem to wrap my head around the day part. Some times parenthesis error or sometime dateadd error.

--This part works fine
STR(DATEDIFF('year', [start_dt], [end_dt])) +  'Year' + ' '+
STR((DATEDIFF('month', DATEADD('year', DATEDIFF('year', [start_dt] , [end_dt]), [start_dt]), [end_dt])))+' months'

-- This is where the issue is
STR(((DATEDIFF('day', DATEADD('month', DATEDIFF('month', DATEADD('year', DATEDIFF('year', [start_dt], [end_dt])),[start_dt], [end_dt]), [start_dt]), [end_dt] ))) + ' day'
Solution

  • Three separate calculations using different date_parts which you should be able to concatenate:

    DATEDIFF('year', [start_date], [end_date])

DATEDIFF('month', DATEADD('year', -DATEDIFF('year', [start_date], [end_date]), [end_date]), DATEADD('year', -DATEDIFF('year', [start_date], [end_date]), [start_date]))

DATEDIFF('day', DATEADD('month', -DATEDIFF('month', DATEADD('year', -DATEDIFF('year', [start_date], [end_date]), [end_date]), DATEADD('year', -DATEDIFF('year', [start_date], [end_date]), [start_date])), DATEADD('month', -DATEDIFF('month', DATEADD('year', -DATEDIFF('year', [start_date], [end_date]),[end_date]),DATEADD ('year' ,- DATEDIFF (' year ',[ start _ date ],[ end _ date ]),[ start _ date ])))

    Overall this should produce the wanted concatenation (ultra ugly as it is):

    STR(DATEDIFF('year', [start_date], [end_date])) + " Year(s), " + STR(DATEDIFF('month', DATEADD('year', -DATEDIFF('year', [start_date], [end_date]), [end_date]), DATEADD('year', -DATEDIFF('year', [start_date], [end_date]), [start_date]))) + " Month(s), " + STR(DATEDIFF('day', DATEADD('month', -DATEDIFF('month', DATEADD('year', -DATEDIFF('year', [start_date], [end_date]),[end_date]),DATEADD ('year' ,- DATEDIFF (' year ',[ start _ date ],[ end _ date ]),[ start _ date ])), DATEADD('month', -DATEDIFF('month', DATEADD('year', -DATEDIFF('year',[ start _ date ],[ end _ date ]),[ end _ date ]),DATEADD (' year ',- DATEDIFF (' year ',[ start _ date ],[ end _ date ]),[ start _ date ])))) + " Day(s)"

    nb: This could include zeros e.g. "1 Year(s) 0 Month(s) 0 Day(s)"