Hi,
We would like acumatica to display the first date of a week. For instance: Today it should show 08/28/2023. How can we achieve this?
We tried : DateAdd(Today(),'w',-3) and it displays Tuesday’s Date 08/29/23 and not 08/28/23.
What would be the best way to do this? I have this formula working in SQL :DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE())-1,0) but cannot figure out how to replicate in acumatica.
Best answer by lauraj46
Hi
There seem to be some idiosyncracies with the date function in report designer. You can use CDate to cast 1/1/1900 as a date, but I had to adjust the first date forward to 5/7/1900 to get the correct date as an end result. Not sure why it should be 5/7/1900, but this formula does seem to work:
=DateAdd(CDate('5/7/1900'), 'w', DateDiff('w', CDate('1/1/1900'), CDate(Today()))-1)
A different approach is to build the formula based on the day of the week. The idea is to subtract a certain number of days based on the day of the week.
Beware though. In my experiments the DayOfWeek function operates differently in a GI vs the Report Designer. In a GI the DayOfWeek function returns 1-7 beginning with Sunday. In the Report Designer, the DayOfWeek function appears to return 0-6 beginning with Monday.
In the Report Designer, I was able to get the correct behavior using this formula:
=IIf(DayOfWeek(Today())=0, DateAdd(Today(), 'd', -13), DateAdd(Today(), 'd', -1*DayOfWeek(Today())-6))
Hope that helps!
Laura
+8Hi
Try this formula:
=DateAdd('1/1/1900', 'w', DateDiff('w', '1/1/1900', Today())-1)
Laura
Hi
+8Hi
Glad that worked! It will change dynamically based on the current date (Today() ).
Laura
Hey
+8Hi
There seem to be some idiosyncracies with the date function in report designer. You can use CDate to cast 1/1/1900 as a date, but I had to adjust the first date forward to 5/7/1900 to get the correct date as an end result. Not sure why it should be 5/7/1900, but this formula does seem to work:
=DateAdd(CDate('5/7/1900'), 'w', DateDiff('w', CDate('1/1/1900'), CDate(Today()))-1)
A different approach is to build the formula based on the day of the week. The idea is to subtract a certain number of days based on the day of the week.
Beware though. In my experiments the DayOfWeek function operates differently in a GI vs the Report Designer. In a GI the DayOfWeek function returns 1-7 beginning with Sunday. In the Report Designer, the DayOfWeek function appears to return 0-6 beginning with Monday.
In the Report Designer, I was able to get the correct behavior using this formula:
=IIf(DayOfWeek(Today())=0, DateAdd(Today(), 'd', -13), DateAdd(Today(), 'd', -1*DayOfWeek(Today())-6))
Hope that helps!
Laura
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
