sql case表达式中报告日期更改的代码

vatpfxk5 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(273)

CASE  
   WHEN CAST([SYS_Created_On] as time) >= CAST('16:00' as time) 
      THEN DATEADD(dd, 1, CAST([SYS_Created_On] AS DATE))
      ELSE CAST([SYS_Created_On] AS DATE)  
END AS [Reported Date],
CASE 
   WHEN CAST([resolved_at] as time) >= CAST('16:00' as time) 
      THEN DATEADD(dd, 1, AS([resolved_at] AS DATE)) 
      ELSE CAST([resolved_at] AS DATE) 
END AS [Resolved Date]

我每天下午4点以后得到周一到周五的数据。如果报告日期是4点以后的星期五，或者报告日期是星期一的星期六或星期天，有人能帮我把它编码成这样吗。

sql sql-server sql-view case

来源：https://stackoverflow.com/questions/63270792/code-reported-date-change-in-sql-case-expression

1条答案

按热度按时间

km0tfn4u1#

如果我理解正确，你需要有一个报告日期为下周一，如果收到的数据和时间是下午4:00之后，或 SYS_Created_On 是星期六还是星期天你需要得到与星期一对应的日期
虽然有不同的方式更优雅（从代码较少的意义上说），但下面的描述更具描述性。

CASE  
    WHEN CAST([SYS_Created_On] as time) >= CAST('16:00' as time) and DATENAME(WEEKDAY,CAST([SYS_Created_On] AS DATE)) = 'Friday' 
      THEN DATEADD(dd, 3, CAST([SYS_Created_On] AS DATE))
    WHEN DATENAME(WEEKDAY,CAST([SYS_Created_On] AS DATE)) = 'Saturday' 
      THEN DATEADD(dd, 2, CAST([SYS_Created_On] AS DATE))
    WHEN DATENAME(WEEKDAY,CAST([SYS_Created_On] AS DATE)) = 'Sunday' 
      THEN DATEADD(dd, 1, CAST([SYS_Created_On] AS DATE))
    WHEN CAST([SYS_Created_On] as time) >= CAST('16:00' as time) 
      THEN DATEADD(dd, 1, CAST([SYS_Created_On] AS DATE))
      ELSE CAST([SYS_Created_On] AS DATE)  
END AS [Reported Date],

赞(0）回复(0）举报 2021-07-24

我来回答

sql case表达式中报告日期更改的代码

1条答案

相关问题

热门标签

最新问答