Using Postgres to calculate EOM date in a WF

Avatar

Avatar
Validate 10
Level 3
holmes74338504
Level 3

Likes

17 likes

Total Posts

51 posts

Correct reply

5 solutions
Top badges earned
Validate 10
Validate 1
Boost 5
Boost 3
Boost 10
View profile

Avatar
Validate 10
Level 3
holmes74338504
Level 3

Likes

17 likes

Total Posts

51 posts

Correct reply

5 solutions
Top badges earned
Validate 10
Validate 1
Boost 5
Boost 3
Boost 10
View profile
holmes74338504
Level 3

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
Establish
MVP
wodnicki
MVP

Likes

976 likes

Total Posts

1,096 posts

Correct reply

514 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

976 likes

Total Posts

1,096 posts

Correct reply

514 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
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)