17
Views
9
Comments
Solved
Calculate a Week of a Month
Application Type
Reactive

Hello,


I hope you are fine.


I have one problem and I can't solve it.

I want to do a query to determine a specific week of a month.

I want to write a query to filter the data between Monday and Sunday.


For example, now I am on 13 January 2021 and I did a BankMovement,  yesterday I did another one and tomorrow and the next days I'll do more BankMovements. So, I want to write a query to catch the data "BankMovement.Date" between Monday and Sunday (11-17 January)


I did this condition but I don't know how I can calculate a Week.

I used the DiffDays(), AddDays() functions, but don't work correctly.


How can I calculate a week?

Can you help me, please?


Thanks in advance.


Best regards,

FC

Rank: #94
Solution

Hello again Fábio,

I think that's going to be a bit more complex then. Unfortunately there is not built-in function that gives you that information, so you need to implement it out of the box.

Since you want to consider a week always as Monday - Sunday, you will need to calculate these 2 days based on your current date.

  • Calculate the Monday of the week based on the current date:
DateTimeToDate(
    If( DayOfWeek(CurrDate()) = 1, CurrDate(),
        If( DayOfWeek(CurrDate()) = 2, AddDays(CurrDate(), -1),
            If( DayOfWeek(CurrDate()) = 3, AddDays(CurrDate(), -2),
                If( DayOfWeek(CurrDate()) = 4, AddDays(CurrDate(), -3),
                    If( DayOfWeek(CurrDate()) = 5, AddDays(CurrDate(), -4),
                        If( DayOfWeek(CurrDate()) = 6, AddDays(CurrDate(), -5),
                            If( DayOfWeek(CurrDate()) = 0, AddDays(CurrDate(), -6),
                                CurrDate()
                            )
                        )
                    )
                )
            )
        )
    )
)

Basically, I'm using DayOfWeek() and AddDays() built-in functions to calculate the corresponding Monday. As an example, one of the conditions is:

If ( DayOfWeek(CurrDate()) = 3, AddDays(CurrDate(), -2)

This means that if your CurrDate() is a Wednesday, DayOfWeek() function will return 3, so you have to remove 2 days to get Monday. If your CurrDate() is a Friday, DayOfWeek() function will return 5, so you have to remove 4 days to get Monday. The rest of the conditions are the same, but for the several days of the week.


  • Calculate the Sunday of the week based on the current date:
DateTimeToDate(
    AddDays(
        If( DayOfWeek(CurrDate()) = 1, CurrDate(),
            If( DayOfWeek(CurrDate()) = 2, AddDays(CurrDate(), -1),
                If( DayOfWeek(CurrDate()) = 3, AddDays(CurrDate(), -2),
                    If( DayOfWeek(CurrDate()) = 4, AddDays(CurrDate(), -3),
                        If( DayOfWeek(CurrDate()) = 5, AddDays(CurrDate(), -4),
                            If( DayOfWeek(CurrDate()) = 6, AddDays(CurrDate(), -5),
                                If( DayOfWeek(CurrDate()) = 0, AddDays(CurrDate(), -6),
                                    CurrDate()
                                )
                            )
                        )
                    )
                )
            )
    ), 6)
)

The rational is basically the same to find Monday, but I'm adding 6 days at the end of the calculation to get the corresponding Sunday.


Since these expressions are very long and hard to maintain, I hardly suggest that you don't use them directly in your query.

Please consider to create functions in order to abstract this entire logic.


For example, you can create 2 functions:

  • CalculateMondayOfTheCurrentWeek that returns the Monday as a Date (first expression)


  • CalculateSundayOfTheCurrentWeek that returns the Sunday as a Date (second expression)


Then back to your query, your expression should be something like:

BankMovement.Date >= CalculateMondayOfTheCurrentWeek()
and
BankMovement.Date <= CalculateSundayOfTheCurrentWeek()


I did some unit tests for some scenarios and it seems to be working properly. But I strongly advise you to test this logic for all possible scenarios (examples: 2 different months in the same week, 2 different years in the same week, etc.).


Hope that this helps you!


Kind regards,

Rui Barradas

Rank: #94

Hello there Fábio,

Hope you are doing well.

So you want to validate if your date is in the current week based on the current day, is that it?


If so it would be something like:

BankMovement.Date >= CurrDate() and BankMovement.Date <= DateTimeToDate(AddDays(CurrDate(), 6))


For this day (CurrDate), it will return the dates between 13-01-2021 and 19-01-2021.


Hope that this helps you!


Kind regards,

Rui Barradas

Rank: #973

hi Fábio

if you are using sql server, you can use DATEPART function and try something like this


Rank: #369

Hi Fabio,

You can use Week_GetMonday of WebPatterns to get the first day of a week.
So in your case, if you provide 13-01-2021, the action will return 11-01-2021.

To get all transactions between a date using SQL, you can just compare dates, so, if FirstDayofWeek is the date of Monday:

BankMovement.Date >= FirstDayOfWeek and BankMovement.Date < AddDays(FirstDayOfWeek, 7)

Regards,
Lennart