Sql查询操作给定日期中的日期、年份和月份

Question

更新:由于某些原因，纪念日日期在表中不正确，因此希望将日期向前推。正在尝试将周年日期更新到下一个日期，该员工的周年日期已超过当前日期。

我的表中有一些不正确的周年纪念日期，我想编写一个逻辑来解决这个问题。逻辑是：

用例1 :员工1周年纪念日= 15th June,2020，Current date = 26,June,2022 (已经在数据库中)。This is invalid date。

员工1周年纪念日应为= 1st July,2022 (New update date)。This will be a correct date.

用例2 : Employee 2周年纪念日= 15th June,2020，Current date = 26,July,2022 (已经在数据库中).This is invalid date

员工2周年纪念日应为= 1st July,2023 (New update date).This will be a correct date.

用例3:员工3周年纪念日= 1st February,2022，current date = 26,July,2022 (already in database)。

员工3周年纪念日应为= 1st March,2023 (New update date)

Sql查询：

Update Employee
set Anniversarydate =  CASE 
            WHEN (YEAR(Anniversarydate) < YEAR(GETDATE()) and
                        month(Anniversarydate) < Month(GETDATE())) 
            then 'manipulate date here'--How do i manupulate date here with my logic
            ELSE 'do nothing'
            End
where (YEAR(Anniversarydate) < YEAR(GETDATE())) and (YEAR(Anniversarydate) < YEAR(GETDATE()) and
month(Anniversarydate) < Month(GETDATE()))

有人能帮我或指点我吗？

Answer 1

因此，正如注释中提到和讨论的那样，有一种解决方案可以在不更改数据库的情况下为您提供更新的日期，因为员工的anniversary date是一个重要的日期，不应该更改：

SELECT Anniversarydate, 
       case when DATEADD(YEAR, 
                         DATEDIFF(Year,Anniversarydate,GETDATE()),
                                  Anniversarydate) < GETDATE() 
          then DATEADD(d, 1, EOMONTH(GETDATE()))
          else DATEADD(YEAR,
                       DATEDIFF(Year,Anniversarydate,GETDATE()),
                       Anniversarydate) end computed
  FROM Employee

现在，如果您仍然想要更新它，下面是您需要使用的命令：

update Employee 
   set Anniversarydate = case when DATEADD(YEAR, 
                         DATEDIFF(Year,Anniversarydate,GETDATE()),
                                  Anniversarydate) < GETDATE() 
          then DATEADD(d, 1, EOMONTH(GETDATE()))
          else DATEADD(YEAR,
                       DATEDIFF(Year,Anniversarydate,GETDATE()),
                       Anniversarydate) end
 where Anniversarydate < GETDATE();

检查它在这里的工作：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9322138cb4827100946bf6b6f682eaf8

更新了--这将给你一个月的第一天，即使是在将来。

update Employee 
   set Anniversarydate = case when DATEADD(YEAR, 
                         DATEDIFF(Year,Anniversarydate,GETDATE()),
                                  Anniversarydate) < GETDATE() 
          then DATEADD(d, 1, EOMONTH(GETDATE()))
          else DATEADD(DAY, 1, EOMONTH(DATEADD(d, 1, DATEADD(YEAR,
                                     DATEDIFF(Year,Anniversarydate,GETDATE()),
                                     Anniversarydate)),-1)) end
 where Anniversarydate < GETDATE();

看到它在这里工作：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=49534bf70138730bb2b782b0ca2b2e8b

更新2

update Employee 
   set Anniversarydate = 
 case when year(Anniversarydate) < year(GETDATE()) 
            then DATEADD(d, 1, EOMONTH(GETDATE()))
          when year(Anniversarydate) = year(GETDATE()) and month(Anniversarydate) < month(GETDATE())
            then DATEADD(DAY, 1, EOMONTH(DATEADD(d, 1, DATEADD(YEAR,
                                     DATEDIFF(Year,Anniversarydate,GETDATE()),
                                     Anniversarydate))))
          else DATEADD(DAY, 1, EOMONTH(DATEADD(d, 1, DATEADD(YEAR,
                                     DATEDIFF(Year,Anniversarydate,GETDATE()),
                                     Anniversarydate))))
          end;

看到它在这里工作：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6ca3e49a8482c6014fc08c1e230356d6