22,210
社区成员
发帖
与我相关
我的任务
分享
WITH CTE
AS
(SELECT [入职日期],DATEADD(dd,365,[入职日期]) AS 显示日期
FROM t
)
SELECT CTE.入职日期,(CASE WHEN CTE.显示日期<'2020-01-01' THEN '2020-01-01' ELSE CTE.显示日期 END) 显示日期 FROM CTE
WITH CTE
AS
(SELECT [入职日期],DATEADD(dd,365,[入职日期]) AS 显示日期
FROM t1
)
SELECT CTE.入职日期,IIF(CTE.显示日期<'2020-01-01','2020-01-01',CTE.显示日期) 显示日期 FROM CTE
WITH CTE
AS
(SELECT [入职日期],DATEADD(DAY,-1,(DATEADD(YEAR,1,[入职日期]))) AS 显示日期
FROM t
)
SELECT CTE.入职日期,IIF(CTE.显示日期<'2020-01-01','2020-01-01',CTE.显示日期) FROM CTE
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[入职日期] DATETIME
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2014/7/29')
INSERT INTO dbo.[t] VALUES(N'2014/8/13')
INSERT INTO dbo.[t] VALUES(N'2014/8/15')
INSERT INTO dbo.[t] VALUES(N'2018/12/5')
INSERT INTO dbo.[t] VALUES(N'2018/12/29')
INSERT INTO dbo.[t] VALUES(N'2019/2/18')
INSERT INTO dbo.[t] VALUES(N'2019/2/20')
INSERT INTO dbo.[t] VALUES(N'2019/7/8')
INSERT INTO dbo.[t] VALUES(N'2019/7/16')
INSERT INTO dbo.[t] VALUES(N'2019/10/12')
INSERT INTO dbo.[t] VALUES(N'2019/10/14')
select [入职日期],DATEADD(DAY,-1,(DATEADD(YEAR,1,[入职日期]))) AS 显示日期
from t
/*
入职日期 显示日期
2014-07-29 2015-07-28
2014-08-13 2015-08-12
2014-08-15 2015-08-14
2018-12-05 2019-12-04
2018-12-29 2019-12-28
2019-02-18 2020-02-17
2019-02-20 2020-02-19
2019-07-08 2020-07-07
2019-07-16 2020-07-15
2019-10-12 2020-10-11
2019-10-14 2020-10-13
*/