MS Access获得ISO标准周编号 [英] MS Access get ISO standard week number

问题描述

令我感到惊讶的是，我无法在网上找到任何现有的解决方案，但我只需要一个返回ISO标准周号的SQL函数(即，第1周的开始始终是该年的第一个星期一).

I'm surprised that I can't find any existing solutions to this online but I just need an SQL function that returns an ISO standard week number (i.e. the start of week 1 is always the first Monday of the year).

没有一个DatePart函数选项一致地返回正确的结果.我曾考虑过选项"vbFirstFourDays-从新的一年至少有四天的第一周开始".但今天(1月12日)进行测试将返回第3周，而不是第2周(我的表情是DatePart("ww",Now(),2))

None of the DatePart function options consistently return the correct result. I had thought the option "vbFirstFourDays - Start with the first week that has at least four days in the new year." but testing it for today (12th Jan) returns week 3, not week 2 (my expression is DatePart("ww",Now(),2) )

今年ISO第1周从1月4日开始，明年2月1日开始，去年1月5日.

This year ISO week 1 starts on 4th Jan, next Year the 2nd Jan and last year it was the 5th of Jan.

非常感谢

推荐答案

The DatePart function does indeed calculate the ISO-8601 week number almost^* correctly when it uses vbMonday for the firstdayofweek argument and vbFirstFourDays for the firstweekofyear argument, e.g.,

DatePart("ww", Date(), vbMonday, vbFirstFourDays)

或者直接在Access查询中使用时

or, when used directly in an Access query

DatePart("ww", Date(), 2, 2)

^* 请注意，该错误记录在此处显然从未修复过，因此根据ISO-8601，据报道21世纪的下一个星期一在第53周，而在下一年的第1周:

^* Note that the bug documented here has apparently never been fixed, so the following Mondays in the 21^st century are reported as being in week 53 when according to ISO-8601 they should be in week 1 of the following year:

2003-12-29
2007-12-31
2019-12-30
2031-12-29
2035-12-31
2047-12-30
2059-12-29
2063-12-31
2075-12-30
2087-12-29
2091-12-31

2003-12-29
2007-12-31
2019-12-30
2031-12-29
2035-12-31
2047-12-30
2059-12-29
2063-12-31
2075-12-30
2087-12-29
2091-12-31

这篇关于MS Access获得ISO标准周编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！