Datediff per month

user046467
user046467 Member
in Magic ETL

Hi,

How can I show the datediff per month instead of per day.

I tried the following beastmode:

DATEDIFF(`date1`,`date2`,MONTH())

Can someone point me out what I am missing?

Thanks in advance

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @user046467

    You'll want to use PERIOD_DIFF instead of DATEDIFF:

    PERIOD_DIFF 

    Returns the number of months between months in two date columns. For this to work, the date values must be months in the format YYYYMM.

    PERIOD_DIFF('Month 1', 'Month 2')

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • nick_dessanti
    nick_dessanti Member

    Hey @user046467,

    I would use something like:

    period_diff(date_format(`date_stage_changed`, '%Y%m'),date_format(`date_created`, '%Y%m'))

    This will get the number of months between two dates without having to make sure they are in the correct format to begin with.

    Best,

    Nick

  • craigbosco
    craigbosco Member

    What's the data format of the column you're operating on?

    I tend to use TIMESTAMPDIFF(month, `date1`,`date2`) in mySQL flows.

Categories