By using this website, you agree to our Terms of Use (click here)
Hi
Hoping someone can help me in this. We have a generic inquiry where we want a column in the result grid to show the week in the year for the date fetched from the SOOrder.OrderDate field. I've understood that DATEPART in SQL would get the desired result, but this is not available in generic inquiries. Any idea of how I could achieve this?
I have not tried this, but couldn't you use SOORder.OrderDate - January 1, 2018 to get the number of days, divide by 7 and round up? I think this would output an integer value for the week you're in. Of course you'd have to maintain the "2018" date field unless you did something where you pulled the year of the SOOrder.OrderDate and then applied it to the Jan 1 date field, but I'm not quite sure how to do that.
Thanks for the input! Based on this I got an idea that seems to work. Need to verify a bit, but wanted to share it:
=Ceiling( DateDiff( 's', '1.1.'+CStr( Year([SOOrder.OrderDate] ) ), [SOOrder.OrderDate])/86400.0/7 )
I needed to take the difference in seconds and then set that to weeks by dividing by 7. This way I got it in decimal format and could then use Ceiling to get the correct week. For example 10th of January was first set to week 1(9/7=1.29= 1 in integer value), but with this it is set to week 2 which is correct.
That looks solid to me. I'd not tried concatenating the year on to a date so it's good to see a successful implementation of it. Thanks for sharing your solution!
Using your formula and modifying it: =Ceiling( DateDiff( 'w', '1.1.'+CStr( Year([SOOrder.OrderDate] ) ), [SOOrder.OrderDate]))+1
Note the "+1" at the end. The week difference only seems to return WHOLE weeks between the dates, so on one of my SO's with Date 1/11/18 it returns a "1". So I incremented the formula and the math straightened out.
Hi
So in my case the week number of the year starts on the first Monday of the year and not necessarily from 1 Jan which may fall on a Wednesday.
Here is my notes:
Depends on when first Monday of year
DayofWeek will give a 1 for Sunday, 2 for Monday etc
Check if 1 Jan is a Sunday then use date 2 Jan else take the Dateadd days of (-DayofWeekResult+2) days to get the first Monday from 1 Jan
That then gets the date of the first Monday, works out weeks between date and that Monday date and adds 1 to get the right week number for the Year
=Ceiling(
DateDiff( 'w',
Iif(DayOfWeek( CDate(Cstr( Year([PMTran.Date]))+'-01-01'))=1,CDate(Cstr( Year([PMTran.Date]))+'-01-02'),DateAdd(CDate(Cstr( Year([PMTran.Date]))+'-01-01'),'d',-DayOfWeek( CDate(Cstr( Year([PMTran.Date]))+'-01-01'))+2))
, CDate([PMTran.Date])))
+1
It seems that your 1 Jan is '1.1' but ours in Australia is like SQL yyyy-mm-dd so feel free to amend my above to suit your dates in US databases.
Perhaps this could be of use? It locates Monday of the current week, then calculates the week number. Replace "Today()" with the date field of your choosing.
=Floor(DayOfYear(DateAdd(Today(),'d', 1-DayOfWeek(Today()))) / 7) + 1
This isn't what you want, but you could do the following if you wanted the Week Ending date (not Week Number) for each Sales Order.
You would need to join to the DateInfo table like this:
Then you could add the DateInfo.WeekEnding field to the RESULTS GRID tab.
Online Members
