| 需求 | 歸零寫法 (時分秒 00:00:00) | 保留時間寫法 |
| 本月第一天 | DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0) | DATEADD(DAY, 1-DATEPART(DAY, @Date), @Date) |
| 本月最後一天 | DATEADD(DAY, -1, DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, @Date), 0)) | DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, 1-DATEPART(DAY, @Date), @Date))) |
| 上月第一天 | DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date)-1, 0) | DATEADD(MONTH, -1, DATEADD(DAY, 1-DATEPART(DAY, @Date), @Date)) |
| 上月最後一天 | DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)) | DATEADD(DAY, 0-DATEPART(DAY, @Date), @Date) |
| 下月第一天 | DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date)+1, 0) | DATEADD(MONTH, 1, DATEADD(DAY, 1-DATEPART(DAY, @Date), @Date)) |
| 下月最後一天 | DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date)+2, 0)) | DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEADD(DAY, 1-DATEPART(DAY, @Date), @Date))) |
| 需求 | 語法 (週日為週首) | 語法 (週一為週首) |
| 本週第一天 | DATEADD(WEEK, DATEDIFF(WEEK, -1, @Date), -1) | DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0) |
| 本週第二天 (週一/週二) | DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0) | DATEADD(WEEK, DATEDIFF(WEEK, 6, @Date), 6) |
| 上週第一天 | DATEADD(WEEK, -1+DATEDIFF(WEEK, -1, @Date), -1) | DATEADD(WEEK, -1+DATEDIFF(WEEK, 0, @Date), 0) |
| 下週第一天 | DATEADD(WEEK, 1+DATEDIFF(WEEK, -1, @Date), -1) | DATEADD(WEEK, 1+DATEDIFF(WEEK, 0, @Date), 0) |