34,588
社区成员
发帖
与我相关
我的任务
分享
create table Emps
(EmpNum varchar(10) not null,
入职时间 date,
工龄 as datediff(yy,入职时间,getdate()))
insert into Emps(EmpNum,入职时间)
select 'E001','2009-06-05' union all
select 'E002','2012-01-05'
select * from Emps
/*
EmpNum 入职时间 工龄
---------- ---------- -----------
E001 2009-06-05 9
E002 2012-01-05 6
(2 row(s) affected)
*/
select datediff(year,入职日期,isnull(离职日期,当前日期))
if not object_id(N'T') is null
drop table T
Go
Create table T([Id] int,[AddTime] Date,[GL] INT)
新建触发器:
CREATE TRIGGER dbo.T_GL_TRI
ON [dbo].T
FOR INSERT
AS
UPDATE T
SET GL = DATEDIFF(MONTH, T.AddTime, GETDATE())
FROM Inserted
WHERE Inserted.Id = T.Id;
GO
插入数据:
Insert T(ID,AddTime)
select 1,'2018-09-01' UNION
SELECT 2,'2017-10-01'
测试:
Select * from T
结果:
create table aa2(indate datetime,dates as datediff(year,indate,getdate()))
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Id] int,[AddTime] Date,[GL] AS DATEDIFF(MONTH,AddTime,GETDATE()))
Insert #T
select 1,'2018-10-01'
Go
--测试数据结束
Select * from #T
USE tempdb
GO
IF OBJECT_ID('user_info') IS NOT NULL DROP TABLE user_info
GO
CREATE TABLE user_info(
id INT IDENTITY(1,1) PRIMARY KEY,
userName NVARCHAR(20) NOT NULL,
entryTime DATETIME NOT NULL
)
GO
INSERT INTO user_info(userName,entryTime) VALUES('小明','2008-12-01')
INSERT INTO user_info(userName,entryTime) VALUES('小刚','2017-10-01')
---- 以上为测试表及测试数据 -----
--加多一个计算列就可以了, 以后不用自己去换算
ALTER TABLE user_info ADD workYears AS DATEDIFF(YEAR,entryTime,GETDATE())
GO
SELECT * FROM user_info AS ui
/*
id userName entryTime workYears
----------- -------------------- ----------------------- -----------
1 小明 2008-12-01 00:00:00.000 10
2 小刚 2017-10-01 00:00:00.000 1
*/
select datediff(year,入职日期,当前日期)