基于服务日期列的年初至今月份开始和结束日期

发布于 2025-01-20 16:33:54 字数 493 浏览 0 评论 0原文

我需要根据“服务日期”列创建月份开始日期和月份结束日期列。

如果服务日期是当前月份，则月结束日期应为今天的日期。

例如：

如果服务日期为“2022-02-14”，则与该日期关联的月份开始日期和月份结束日期为“2022-01-01”和“2022-02-28”。

但是，如果服务日期为“2022-04-07”（当前月份），则与该日期关联的月份开始日期和月份结束日期应为“2022-04-01”和“2022-04-11”（今天的日期）。

每月的常规开始和结束日期：

CAST(DATEADD(month, DATEDIFF(month, 0, @ServiceDate), 0) AS DATE) AS [Month Start Date],
CAST(EOMONTH(@ServiceDate) AS DATE) AS [Month End Date],

原文

I need to create Month Start Date and Month End Date columns based on the Service Date column.

If Service Date is current month, then Month End date should be Today's Date.

For example:

If Service date was '2022-02-14', then Month Start and Month End dates associated with the date are '2022-01-01' and '2022-02-28'.

However, if Service Date is '2022-04-07' (Current Month), then Month Start and Month End dates associated with the date should be '2022-04-01' and '2022-04-11' (Today's Date).

Regular Start and End Dates of the Month:

CAST(DATEADD(month, DATEDIFF(month, 0, @ServiceDate), 0) AS DATE) AS [Month Start Date],
CAST(EOMONTH(@ServiceDate) AS DATE) AS [Month End Date],

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

德意的啸 2025-01-27 16:33:54

您已经知道了本月的第一个逻辑。在本月底，如果是本月，请尝试返回今天的日期。否则，返回@Servicedate月份的最后一天

SELECT CAST(DATEADD(month, DATEDIFF(month, 0, @ServiceDate), 0) AS DATE) AS [Month Start Date]
      , CASE WHEN dateDiff(month, @ServiceDate, getDate()) = 0 THEN CAST(getDate() AS DATE) 
             ELSE CAST(EOMONTH(@ServiceDate) AS DATE) 
        END AS [Month End Date]

You already have the first of the month logic figured out. For the end of the month, try a case to return today's date if it's the current month. Otherwise, return the last day in the @ServiceDate month

SELECT CAST(DATEADD(month, DATEDIFF(month, 0, @ServiceDate), 0) AS DATE) AS [Month Start Date]
      , CASE WHEN dateDiff(month, @ServiceDate, getDate()) = 0 THEN CAST(getDate() AS DATE) 
             ELSE CAST(EOMONTH(@ServiceDate) AS DATE) 
        END AS [Month End Date]

回复收藏 0 原文

~没有更多了~