Issue with DatePart function in Queries

[ruperman]

Hello,

I've been trying to create a query that will take a date and return the week number of the date.

My original date is formatted m/d/yyyy

I need my weeks to start on Monday and I would like the week containing Jan 1st to be the first week of the year

I have tried using the following function:
DatePart("ww",#12/31/2001#,2,1)

but the I get a result of 53 in this query, when I expected/need it to be 1. Can someone please point out what I"m doing wrong here? Any help would be appreciated. Thank you.

 

[plog]

Trying to hammer weeks into years never works. In non-leap years there are 365 days in a year. 365/7 equals 52 with a remainder of 1. Over time that remainder is going to add up and where does that week go? It has to go somewhere and over time, depending on how you handle it, it will push day 1 of week 1 further and further away from January 1st...

...or as in your case, it can cause a specific day to fall into 2 different years. 12/31/2001 is both day 1 of week 53 in 2001 and day 1 of week 1 in 2002. Access had to choose one value to give you, so it gave the earlier one which made it week 53.

Do the math-- 1/1/2001 started on a Monday, so that makes 1/1/2001 day 1 of week 1 of 2001. If you grab a 2001 calendar and start counting Mondays-- you will see that 12/31/2001 is a monday which means its day 1 of week 53 of 2001 per your definition. Further day 1 of week 1 of 2002 starts on that exact same day.

Weeks and years don't work well together.

 

[namliam]

Could this be "your pain" as well?

http://www.access-programmers.co.uk/forums/showthread.php?t=259883

 

[ruperman]

Hi plog, thank you for the explanation, but I thought that the point of the last constant in the function DatePart("ww",#12/31/2001#,2,1) is to tell the function that if the week contains January 1st, then it should be counted as the first week of the new year and not the last week of the current year. Please let me know if I misunderstood the logic behind that.

Hi namliam, the issue looks similar. The only difference is that the link you gave me was for when the second constant =2, and I need it for when it =1. Nonetheless, I think the workaround listed might work and I will try to use it for this specific instance.

In addition, there is another similar problem I am getting where if I try to get the year for this same date, it is also returning an unexpected value:

DatePart("yyyy",#12/31/2001#,2,1)

Returns 2001. I expect it to return 2002 for the same reasons as in my original post. I did not see any workaround for this issue in the link provided.

Any help would be appreciated.

 

[plog]

Actually, based on the fact that you are getting 53 for the week part, it only makes sense that the year part returns 2001 for 12/31/2001. Access may be wrong, but at least its consistent.

No matter how you slice it or dice it, because of that remainder 1 (365/7) every year will span parts of 53 weeks (there's some leap years where it spans into 54 weeks). Square peg, round hole--that day has to go somewhere or they will add up over time.

My suggestion is to devise your own definition and then build a custom function to implement it. But, be careful, its very hard to accurately define a 1-1 mapping of dates into weeks/years. Search my post history, I recall helping someone lock down a definition within the last 3 months.