Little help on format a date for an insert

Wappler General Need Help
1

Hi
hoping someone can help me figure out how to do a date insert into a table.

I need the date to be NOW + 7 working days (mon to fri) days but the time to be 09:00 am, as it is a legal notice we issue that expires on that date and time so the time is importnat.

something like this

NOW.formatDate('yyyy-MM-dd'.dateAdd('days', 7), false) +'09:00'

that of course does not work, hopefully its something that is simple and just to get the right order of things.

thanks in advance

2

The correct expression is this:

NOW.dateAdd('days', 7).formatDate('yyyy-MM-dd 09:00', false)

Mmm can you explain a little more about this?

1 Like
3

The amount of days to add will depend on which day of the week it starts counting. You could do something like this

NOW.dateAdd('days', (NOW.formatDate('w') == 6 ? 10 : NOW.formatDate('w') > 3 ? 11 : 9) ).formatDate('yyyy-MM-dd 09:00', false)

Explanation:

.formatDate('w') returns the day of the week 0=Sunday, 1= Monday...6=Saturday

(NOW.formatDate('w') == 6 ? 10 : NOW.formatDate('w') > 3 ? 11 : 9)

If it's Saturday (day 6) 10 days need adding (7 days plus Sunday and following weekend = total 10 days)
Thur and Fri (4-5) it needs 11 (7 days plus it will span two full weekends)
Sun to Wed (0-3) will add just 9 days (7 days plus one full weekend)

The rest is as @franse suggested

One thing to note is that this doesn't account for bank/public holidays, if that makes a difference to you

3 Likes
4

@bpj you gotta to be kidding me!
I've been using SELECT DAYOFWEEK(:P1) all this time!
Plus, in the past I used to have a separated table with the week days of specific dates..

@George can this be added on the UI?
image

5

Ah. Hang on. the formatDate options might be PHP only - maybe @patrick can confirm?

6

looking at app/lib/core/util.js it is currently only PHP but it should be really easy to add it in:

  1. find the formatDate function (line 141)
  2. add a w (anwhere in the yMdHhmsaAv bit) into the regular expression (line 157) e.g.
return format.replace(/([yMdHwhmsaAv])(\1+)?/g, part => {

  1. add case 'w': return w; to the end of the switch cases
    image

  2. Save and test

If something doesn't work (not that it should), you can reset the file using the Project Asset Updater tool

image
image860×423 46.6 KB

1 Like
7

thanks, will give that a try, lot more complex that I thought lol :slight_smile:

will report back how i get one, thanks again everyone

8

worked a treat, thank you all for that.

1 Like