SQL Datepart添加年份

Question

我有一个从1960年开始到2016年的日期列表。我想获取日期的月份和日期，然后：

   - If Month and Day > Today then add 2016 as Yr BUT
   - If Month and Day < Today then add 2017 as Yr

以下是我到目前为止使用的SQL：

Select DATEPART(MM, Date) as Month,
  DATEPART(dd, Date) as DayNum
  From DateTable
  WHERE CategoryID = 3

Answer 1

如下所示：

select (case when month(date) * 100 + day(date) < month(getdate()) * 100 + day(getdate())
             then datefromparts(2017, month(date), day(date)
             else datefromparts(2016, month(date), day(date)
        end) as next_anniversary
from datetable
where categoryid = 3;

你实际上不需要做算术。这相当于：

select (case when month(date) < month(getdate()) or
                  month(date) = month(getdate()) and day(date) < day(getdate())
             then datefromparts(2017, month(date), day(date)
             else datefromparts(2016, month(date), day(date)
        end) as next_anniversary
from datetable
where categoryid = 3;

此外，SQL Server 2012+中还提供了datefromparts()。您可以在SQL Server的早期版本中拼凑出类似的功能，但这更简单。

Answer 2

这是一种简单的方法。工作正常

SELECT CASE WHEN SUBSTRING(CAST(CAST(Date AS DATE) AS NVARCHAR),6,10)<SUBSTRING(CAST(CAST(GETDATE() AS DATE) AS NVARCHAR),6,10)
THEN datefromparts(2017, month(Date), day(Date))
ELSE datefromparts(2016, month(Date), day(Date)) END
FROM datetable
WHERE categoryid = 3;

如果对你也有效，请让我知道。

如果您运行的是SQL Server2008，DATEFROMPARTS将无法工作，请使用

SELECT CASE WHEN SUBSTRING(CAST(CAST(Date AS DATE) AS NVARCHAR),6,10)<SUBSTRING(CAST(CAST(GETDATE() AS DATE) AS NVARCHAR),6,10)
THEN DATEADD(YEAR, 2017-YEAR(ex_date), Date)
ELSE DATEADD(YEAR, 2016-YEAR(ex_date), Date)
END
FROM datetable
WHERE categoryid = 3;

Answer 3

对于2008年的SQL Server版本，请使用以下查询。

     SELECT CASE WHEN month(date) < =month(getdate())  and day(date) < day(getdate()) 
                   THEN CONVERT(DATE,'2017-'+
                              CAST(Month(date) AS VARCHAR(2))+'-'+CAST(Day(date) AS VARCHAR(2)))
                    ELSE CONVERT(DATE,'2016-'+
                             CAST(Month(date) AS VARCHAR(2))+'-'+CAST(Day(date) AS VARCHAR(2))) END as new_date
     FROM datetable
     WHERE categoryid = 3;