Week # of the Month - DATEPART

Epicor ERP 10
1

I am trying to get to the week # of the MONTH (not the week number of the year). In Crystal reports I have a handy little formula that returns this information, however I don’t know what the syntax would be for a BAQ.

(Week number is decided by determining where the first Sunday of the month falls. For example: 02/21/24 would be the 2nd month the 4th week because week ending 02/04/24 is considered the first week of the month)

CRYSTAL REPORTS FORMULA (actually googled this) :grinning:
image

Any help would be greatly appreciated! Jill

2

SQL also has DatePart, so something similar should work in a BAQ.

3

I figured as much, but I’m new to BAQs, it seems slightly different then Crystal formulas. Getting that syntax right is a challenge. Thanks for your reply. Jill

4

You need a sql statement in the BAQ calculated field. So this isn’t something unique to Epicor. I found this:

It’s also easier to debug queries in ssms if that is an option for you.

1 Like
5

thank you

6

in a BAQ, you simply create a calculated field, and name it whatever you want. then put the datepart calcuation into it

DATEPART(week, OrderHed.OrderDate)
7

That gives you week number (of the year)… OP was looking for week number (of the month).

Is there a way to do that with Calc field?

8

One should be able to use the formula found here:

sql server - TSQL Calculate week number of the month - Stack Overflow

 datepart(wk, GETDATE()) - datepart(wk,dateadd(m, DATEDIFF(M, 0, GETDATE()), 0)) + 1

I see that this is the same solution that @TerryR posted above.

1 Like