Using Postgres to calculate EOM date in a WF

Avatar

Avatar

holmes74338504

Avatar

holmes74338504

holmes74338504

14-03-2019

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?

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

wodnicki

MVP

Avatar

wodnicki

MVP

wodnicki
MVP

18-03-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

Answers (0)