Using Postgres to calculate EOM date in a WF | Community
Skip to main content
H
holmes74338504
Level 3
March 14, 2019
Solved

Using Postgres to calculate EOM date in a WF

  • March 14, 2019
  • 1 reply
  • 2312 views

Hello,

In my workflow - I need to calculate an end of month date base on Month and Year I have in the table. It was suggested that I use Postgres functions to do so, but I don't know how. Sorry - just getting to learn this tool, so many things are new to me. I was trying to do this in an Enrichment so I can add the final date to an already existing field in my recipient schema (I'm updating some old date where this field was not initially captured).

I know in JS I would just code it

var enddate = new Date(year, month+1,0);

recipient@newDate = enddate,toISOString();

But how can I access postgres functions, to allow me to use SQL funtctions  to create my desire output?

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Jonathon_wodnicki

    Hi,

    Use this, here GetDate() is the date you're getting eom for:

    SubDays(ToDateTime((Month(GetDate()) + 1) + '/1/' + Year(GetDate())), 1)

    Note this should fail for December, which you'd need to special-case. In theory:

    Iif(Month(GetDate())=12,

    '12/31/' + Year(GetDate()),

    SubDays(ToDateTime((Month(GetDate()) + 1) + '/1/' + Year(GetDate())), 1)

    )

    Thanks,

    -Jon

    1 reply

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity AdvisorAccepted solution
    Community Advisor
    March 18, 2019

    Hi,

    Use this, here GetDate() is the date you're getting eom for:

    SubDays(ToDateTime((Month(GetDate()) + 1) + '/1/' + Year(GetDate())), 1)

    Note this should fail for December, which you'd need to special-case. In theory:

    Iif(Month(GetDate())=12,

    '12/31/' + Year(GetDate()),

    SubDays(ToDateTime((Month(GetDate()) + 1) + '/1/' + Year(GetDate())), 1)

    )

    Thanks,

    -Jon

      Terms & ConditionsAccessibility statement

      Loading footer...