27,579
社区成员
发帖
与我相关
我的任务
分享
create table #TT
(
XM varchar(20),
GZRQ datetime,
GL int
)
insert into #TT select 'AA','2008-09-03',1
union all select 'BB','2005-08-12',5
union all select 'CC','2001-06-01',9
select XM,datediff(month,dateadd(month,1,gzrq),getdate())/12 '工龄' from #TT
XM 工龄
-------------------- -----------
AA 1
BB 5
CC 9
(3 行受影响)
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO
---->建表
create table [TB]([XM] varchar(2),[GZRQ] datetime,[GL] int)
insert [TB]
select 'AA','2008-09-03',1 union all
select 'BB','2005-08-12',5 union all
select 'CC','2001-06-01',9
GO
--> 查询结果
SELECT XM,GZRQ,DATEDIFF(MM,DATEADD(MM,-1, GZRQ),GETDATE())/12 AS gl
FROM [TB]
--> 删除表格
--DROP TABLE [TB]
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([XM] varchar(2),[GZRQ] datetime,[GL] int)
insert [TB]
select 'AA','2008-09-03',1 union all
select 'BB','2005-08-12',5 union all
select 'CC','2001-06-01',9
select * from [TB]
SELECT XM,gzrq,GL = DATEDIFF(MONTH,gzrq,DATEADD(MONTH,1,GETDATE()))/12 FROM TB
/*
XM gzrq GL
AA 2008-09-03 00:00:00.000 2
BB 2005-08-12 00:00:00.000 5
CC 2001-06-01 00:00:00.000 9*/
create table #TT
(
XM varchar(20),
GZRQ datetime,
GL int
)
insert into #TT select 'AA','2008-09-03',1
union all select 'BB','2005-08-12',5
union all select 'CC','2001-06-01',9
select XM,datediff(month,gzrq,dateadd(month,1,getdate()))/12 '工龄' from #TT
XM 工龄
-------------------- -----------
AA 2
BB 5
CC 9
(3 行受影响)
datediff(month,gzrq,dateadd(month,1,getdate()))/12