在 Microsoft Access 中，我有一個表，其中每個作業日的作業小時數與專案編號、ISO 周數和年份相關聯。簡化后，它看起來像這樣：
| 專案 ID | 周數 | 年號 | 周一 | 周二 |
|---|---|---|---|---|
| 1 | 1 | 2022 | 5 | 6 |
| 1 | 2 | 2022 | 7 | 8 |
我正在嘗試設定一個查詢，其中所有作業日列（周一到周日）都“合并”到一個列中，并將周數和年份數轉換為日期。它應該是這樣的：
| 專案 ID | 日期 | 每天小時數 |
|---|---|---|
| 1 | 03.01.2022 | 5 |
| 1 | 04.01.2022 | 6 |
| 1 | 2022 年 1 月 10 日 | 7 |
| 1 | 2022 年 1 月 11 日 | 8 |
我設法使用 UNION 查詢將所有作業日列合并為一列：
SELECT ProjectID, WeekNumber, YearNumber, Monday As HoursPerDay FROM ProjectHours
UNION ALL
SELECT ProjectID, WeekNumber, YearNumber, Tuesday As HoursPerDay FROM ProjectHours;
結果如下所示：
| 專案 ID | 周數 | 年號 | 每天小時數 |
|---|---|---|---|
| 1 | 1 | 2022 | 5 |
| 1 | 1 | 2022 | 6 |
| 1 | 2 | 2022 | 7 |
| 1 | 2 | 2022 | 8 |
但我堅持將 iso-week 和 year 數字轉換為 date。這在 Access-SQL 中是否有可能？
I found this question on Stackoverflow and went through the date/time functions that Microsoft lists for Access but could not get it to work.
Any help is highly appreciated.
EDIT: A community member has suggested a possible duplicate of this question, but I am looking for a solution that I can use in an Access-SQL query, so the suggested duplicate does not help me, as it suggests a VBA based solution.
uj5u.com熱心網友回復：
這不是那么簡單，因為 ISO 年很少與日歷年同步。
但是這個函式會做：
' Returns the date of Monday for the ISO 8601 week of IsoYear and Week.
' Optionally, returns the date of any other weekday of that week.
'
' 2017-05-03. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateYearWeek( _
ByVal IsoWeek As Integer, _
Optional ByVal IsoYear As Integer, _
Optional ByVal DayOfWeek As VbDayOfWeek = VbDayOfWeek.vbMonday) _
As Date
Dim WeekDate As Date
Dim ResultDate As Date
If IsoYear = 0 Then
IsoYear = Year(Date)
End If
' Validate parameters.
If Not IsWeekday(DayOfWeek) Then
' Don't accept invalid values for DayOfWeek.
Err.Raise DtError.dtInvalidProcedureCallOrArgument
Exit Function
End If
If Not IsWeek(IsoWeek, IsoYear) Then
' A valid week number must be passed.
Err.Raise DtError.dtInvalidProcedureCallOrArgument
Exit Function
End If
WeekDate = DateAdd(IntervalSetting(dtWeek), IsoWeek - 1, DateFirstWeekYear(IsoYear))
ResultDate = DateThisWeekPrimo(WeekDate, DayOfWeek)
DateYearWeek = ResultDate
End Function
但是，它使用了一系列支持功能，例如：
' Returns the primo date of the week of the date passed.
'
' 2016-01-13. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateThisWeekPrimo( _
ByVal DateThisWeek As Date, _
Optional ByVal FirstDayOfWeek As VbDayOfWeek = vbSunday) _
As Date
Dim Interval As String
Dim Number As Double
Dim ResultDate As Date
Number = 0
Interval = IntervalSetting(DtInterval.dtWeek)
ResultDate = DateIntervalPrimo(Interval, Number, DateThisWeek, FirstDayOfWeek)
DateThisWeekPrimo = ResultDate
End Function
還有更多 - 在這里發布太多了。
因此，請參閱我在GitHub 上的專案VBA.Date以獲取包含完整代碼的模塊。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/424988.html