Using Postgres to calculate EOM date in a WF

[holmes74338504]

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?

[Jonathon_wodnicki · Accepted solution]

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