Selecting only 'month-day time' from Datetime field in SQL (server 2012)

问题: I'm looking at changing a specific date to a different date in a case statement, however, I only want it to apply to a day, month and time. For example, I want to get th...

问题:

I'm looking at changing a specific date to a different date in a case statement, however, I only want it to apply to a day, month and time.

For example, I want to get the case statement to change any date which falls on 31/12 @ 23:59:00 to 01/01 @ 00:00:00 but unless I write the case to include each year for the next 40 years to cover myself, I've not been able to resolve this.

I am writing this from the UK with date format being dd/mm/yyyy (above example is 31st December and 1st January).

The format of the datetime field in the database is 'datetime': 2019-07-01 13:14:47).


回答1:

I can't tell if you want the return type to be a date or datetime. If a date, you can do:

(case when year(dateadd(minute, 1, datecol)) <> year(datecol)
      then datefromparts(year(datecol) + 1, month(datecol), day(datecol))
      else cast(datecol as date)
 end)

The logic would be similar for a datetime, assuming datecol is already a datetime:

(case when year(dateadd(minute, 1, datecol)) <> year(datecol)
      then datefromparts(year(datecol) + 1, month(datecol), day(datecol))
      else datecol
 end)

回答2:

If I understand correctly you want to round the dates inside last minute of year into the next year. You can do this:

SELECT datecol, CASE
    WHEN MONTH(datecol) = 12 AND DAY(datecol) = 31 AND CAST(datecol AS TIME(3)) >= '23:59:00' THEN CAST(DATEADD(MINUTE, 1, datecol) AS DATE)
    ELSE datecol
END
FROM (VALUES
    (CAST('2018-12-31 23:58:59.997' AS DATETIME)),
    (CAST('2018-12-31 23:59:00.000' AS DATETIME)),
    (CAST('2018-12-31 23:59:59.997' AS DATETIME)),
    (CAST('2019-01-01 00:00:00.000' AS DATETIME))
) AS v(datecol)

Result:

2018-12-31 23:58:59.997    2018-12-31 23:58:59.997
2018-12-31 23:59:00.000    2019-01-01 00:00:00.000
2018-12-31 23:59:59.997    2019-01-01 00:00:00.000
2019-01-01 00:00:00.000    2019-01-01 00:00:00.000
  • 发表于 2019-01-11 18:07
  • 阅读 ( 200 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除