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?
Solved! Go to 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
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