Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Using Postgres to calculate EOM date in a WF

Avatar

Level 4

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?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

View solution in original post

1 Reply

Avatar

Correct answer by
Community Advisor

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