topic Re: Date expression query in App Development

Date expression query

Zub — Mon, 30 Sep 2024 08:51:12 GMT

I have the following expression hardcoded for month of september:

= (DueDate >= Date('01/09/2024') AND DueDate <= Date('30/09/2024'))

Is there a way to make the date part dynamic so that for each month I don't have to keep changing it manually? Like for example for next month (october) I will have to manually change the expression to:

= (DueDate >= Date('01/10/2024') AND DueDate <= Date('31/10/2024'))

Thanks for any help

Re: Date expression query

Kushal_Chawda — Mon, 30 Sep 2024 10:20:00 GMT

@Zub you can do it using today() with monthstart() and monthend()

(DueDate >= monthstart(today()) AND DueDate <=monthend(today()))

Re: Date expression query

Zub — Mon, 30 Sep 2024 10:46:18 GMT

Hi @Kushal_Chawda . Many thanks for your reply. That works very well. Just another thing I had not realised is that I'd like to do this at the start of each month for the previous months data. So the expression would need to be for the previous months start and end days.... the monthstart and monthend would have to be for the previous month. How could i tweak the expression you gave to do this? Many thanks for your help

Re: Date expression query

Kushal_Chawda — Mon, 30 Sep 2024 11:00:55 GMT

@Zub you can tweak it as below

(DueDate >= monthstart(addmonths(today(),-1)) AND DueDate <=monthend(addmonths(today(),-1)))

Re: Date expression query

Zub — Mon, 30 Sep 2024 11:15:32 GMT

@Kushal_Chawda That works. Thx!