I need to print some reports and it involve in getting all the dates of weekend (Sat Sun) when user select a date from the interface.
If the Date selected is 2022-09-15 then i will need to get all the Sat Sun Dates
2022-09-03
2022-09-04
2022-09-10
2022-09-11
2022-09-17
2022-09-18
2022-09-24
2022-09-25
Appreciate a solution with any need to install additional components
Thank you in advance :)
Hi,
If you want to see an implementation you have here an OML with an example in Server action and client action. Both returns the list of dates for the weekend days for the given months. You have a screen too in that application to test it.
Hope this can help you.
Best regards,
Ricardo Pereira
Hi Ricardo,
Though I appreciate you took the time to make an example, I think it is best to let novice users try things out themselves. It will increase their knowledge and gives them skills to next time come up with things themselves. Spoonfeeding them with pre-made code is, especially in these simple cases, not something we should do!
Hi Kilian,
Hope you're well.
I respect your opinion, but I don't totally agree.
Many times, novices need a little boost or help to unlock their minds and to understand the possibilities to do something, and some examples like I've done are not bad at all in my opinion. Just like in school, before you put your ands on your exercises, you see examples and some pre built codes. I believe that bad is to do a complete work and/or with some level of complexity.
Don't get me wrong, it's just an opinion based in my experience. I understand yours and you have my full respect on that. :)
Thank you for your effort i had check yours and mine quite the same
Amazing!
I'm glad that you solved your problem!
Thanks for your feedback!
In general, I think a two-step solution is best: first give people some hints on how to do it, so they can apply their own reasoning, and if they aren't able to solve it on their own, then step two would be supplying code. I've seen too many people just copy/paste code "because it works" without thinking for themselves. Nevertheless, I appreciate that you take time to help novice developers!
Hi Jerah,
Do I understand correctly you want to know all dates that fall on a weekend of the month of the date entered? In that case, the easiest way is to start with a NewDate(Year(EnteredDate), Month(EnteredDate), 1), then check if it's a weekend day using the built-in DayOfWeek function (which returns 0 for Sunday and 6 for Saturday), and if so add it to your output list, then increase the date by 1 day (using the built-in AddDays action), again check, etc. until you reach the end of the month.
Of course, you can optimize it a bit by looking at the day of the first of the month, and if it's not a weekend day calculating the first next saturday, then add that saturday plus the next sunday, then loop by adding 7 (for a whole week), but it will in practice not much faster.
Thank you very much i will give your idea a try, appreciate your input :)
You're most welcome :). Let us know if you made it work.
Actually it will be even better if i can get the date result directly from the database. But i am not able to translate the mysql to outsystem sql
I am actually migration this report from another web application
SELECT bookdate FROM bus_reporting where (dayofweek(bookdate) in (1,7) and month(bookdate)='9' and year(bookdate)='2022') or (locate(mid(bookdate,1,10),(select ph from ph where idph=1)) <>0 and month(bookdate)='9' and year(bookdate)='2022') group by bookdate order by bookdate asc
As you can see it will produce the result directly
Anyone is able to translate the above mysql to outsystems ?
syntax for dayofweek/month/year etc is it the same in outsystems sql
There is no "OutSystems SQL" as such. The SQL you enter in an SQL query is either MS SQL or Oracle SQL, depending on the back-end database (typically MS SQL).
Also note that in your query, the "bus_reporting" table apparently already contains all dates that could be a weekend day, which indeed makes it easier than doing it programmatically.
The MS SQL for "dayofweek(date)" is "DATEPART(WEEKDAY, date)". It has the same range (1 = sunday, 7 = saturday).
I'm not sure what you are doing with the LOCATE(MID) and "SELECT PH", what extra dates are you selecting there?
Thank you , based on your input i am able to do it in outsystem :)