--成年未成年要按实岁算,不能直接datediff(yy,'生日',getdate()) --生成测试数据 create table nian(name varchar(20),borndate datetime) insert into nian(name,borndate) select 'a','1988-01-01' union all select 'b','1988-10-10' union all select 'c','1991-06-01' union all select 'c','1991-10-10' --查询,查询结果中,1表示成年,0表示未成年 select name,borndate, [是否成年]=case when datediff(dd, substring(convert(varchar(10),getdate(),120),1,4)+ substring(convert(varchar(10),borndate,120),5,6), getdate())>0 then case when datediff(yy,borndate,getdate())>=18 then '1' else '0' end else case when datediff(yy,borndate,getdate())-1>=18 then '1' else '0' end end from nian
DECLARE @A TABLE (Name CHAR(20),BirthDay DATETIME) INSERT INTO @A SELECT '张三','19910909' UNION ALL SELECT '李四','19821208' UNION ALL SELECT '王五','19920909'SELECT *,DATEDIFF(YY,Birthday,GETDATE()) AS 岁数, DATEDIFF(DAY,DATEADD(YY,18,Birthday),GETDATE()) AS 差几天成年 FROM @A WHERE DATEDIFF(YY,Birthday,GETDATE())<18 OR DATEDIFF(YY,Birthday,GETDATE())=18 AND DATEDIFF(DAY,DATEADD(YY,18,Birthday),GETDATE())<0
结果如下 ---- name borndate 是否成年 a 1988-01-01 00:00:00 1 b 1988-10-10 00:00:00 1 c 1991-06-01 00:00:00 1 c 1991-10-10 00:00:00 0
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- ========================================= if OBJECT_ID('tb') is not null drop table tb go create table tb(name varchar(20),borndate datetime) insert into tb(name,borndate) select 'a','1988-01-01' union all select 'b','1988-10-10' union all select 'c','1991-06-01' union all select 'c','1991-10-10' union all select 'd','2008-10-10' union all select 'e','2003-01-10' go select * from tb where DATEdiff(YY,borndate,GETDATE())<18 /*------------ d 2008-10-10 00:00:00.000 e 2003-01-10 00:00:00.000 -------*/
borndate='1991-12-30' 判读就会错误。。 实际还未成年
if exists (select * from sysobjects where name='tb') drop table tbdeclare @a datetime,@b datetime set @a='1991-10-27' set @b='1991-6-20' create table tb (id int,birth datetime,超过18岁的天数 int) insert into tb select 1,@a,datediff(day,dateadd(year,18,@a),getdate()) union all select 2,@b,datediff(day,dateadd(year,18,@b),getdate())select id,birth,超过18岁的天数,case when 超过18岁的天数 >0 then '是' else '否' end as 是否年满18岁 from tb ---------------------------------------------------------------------------------------------- id birth 超过18岁的天数 是否年满18岁 1 1991-10-27 00:00:00.000 -120 否 2 1991-06-20 00:00:00.000 9 是 超过18岁的天数:为正数说明已经超过18岁的天数,为负数则是到18岁还差的天数。
dateadd(year,18,生日) > getdate() --未成年
if object_id('tb') is not null drop table tb go create table tb(id int identity(1,1),name varchar(10),birthday datetime) go insert into tb select '张三','1989-02-03' union all select '李四','1985-01-01' union all select '王五','1992-10-10' go select id,name,birthday from tb where dateadd(yy,18,birthday)>getdate()
from tb
where datediff(year,生日,getdate())<18
Year(getdate())-Year('19791205')值小于18的为未成年人
or (datediff(yy,[date],getdate())=18 and datediff(day,dateadd(year,18,[date]),getdate())<0 )
--到天的話,再加判斷
where datediff(yy,[date],getdate()) <18
or (datediff(yy,[date],getdate())=18 and datediff(day,dateadd(year,18,[date]),getdate()) <0 )
就肯定是未成年的 ,大于的就不用说了。
现在只有当(Year(getdate())-Year('出生日期'))=18而且为满18岁的,也就是还未过生日的,只要比较月份和日期的值就行了
--成年未成年要按实岁算,不能直接datediff(yy,'生日',getdate())
--生成测试数据
create table nian(name varchar(20),borndate datetime)
insert into nian(name,borndate)
select 'a','1988-01-01' union all
select 'b','1988-10-10' union all
select 'c','1991-06-01' union all
select 'c','1991-10-10'
--查询,查询结果中,1表示成年,0表示未成年
select name,borndate,
[是否成年]=case when
datediff(dd,
substring(convert(varchar(10),getdate(),120),1,4)+
substring(convert(varchar(10),borndate,120),5,6),
getdate())>0
then
case when
datediff(yy,borndate,getdate())>=18
then
'1'
else
'0'
end
else
case when
datediff(yy,borndate,getdate())-1>=18
then
'1'
else
'0'
end
end
from nian
INSERT INTO @A
SELECT '张三','19910909' UNION ALL
SELECT '李四','19821208' UNION ALL
SELECT '王五','19920909'SELECT *,DATEDIFF(YY,Birthday,GETDATE()) AS 岁数, DATEDIFF(DAY,DATEADD(YY,18,Birthday),GETDATE()) AS 差几天成年 FROM @A
WHERE DATEDIFF(YY,Birthday,GETDATE())<18
OR DATEDIFF(YY,Birthday,GETDATE())=18 AND DATEDIFF(DAY,DATEADD(YY,18,Birthday),GETDATE())<0
结果如下
----
name borndate 是否成年
a 1988-01-01 00:00:00 1
b 1988-10-10 00:00:00 1
c 1991-06-01 00:00:00 1
c 1991-10-10 00:00:00 0
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(name varchar(20),borndate datetime)
insert into tb(name,borndate)
select 'a','1988-01-01' union all
select 'b','1988-10-10' union all
select 'c','1991-06-01' union all
select 'c','1991-10-10' union all
select 'd','2008-10-10' union all
select 'e','2003-01-10'
go
select * from tb
where DATEdiff(YY,borndate,GETDATE())<18
/*------------
d 2008-10-10 00:00:00.000
e 2003-01-10 00:00:00.000
-------*/
实际还未成年
if exists (select * from sysobjects where name='tb')
drop table tbdeclare @a datetime,@b datetime
set @a='1991-10-27'
set @b='1991-6-20' create table tb
(id int,birth datetime,超过18岁的天数 int)
insert into tb
select 1,@a,datediff(day,dateadd(year,18,@a),getdate())
union all
select 2,@b,datediff(day,dateadd(year,18,@b),getdate())select id,birth,超过18岁的天数,case when 超过18岁的天数 >0 then '是' else '否' end as 是否年满18岁 from tb
----------------------------------------------------------------------------------------------
id birth 超过18岁的天数 是否年满18岁
1 1991-10-27 00:00:00.000 -120 否
2 1991-06-20 00:00:00.000 9 是
超过18岁的天数:为正数说明已经超过18岁的天数,为负数则是到18岁还差的天数。
dateadd(year,18,生日) > getdate() --未成年
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity(1,1),name varchar(10),birthday datetime)
go
insert into tb
select '张三','1989-02-03' union all
select '李四','1985-01-01' union all
select '王五','1992-10-10'
go
select id,name,birthday from tb where dateadd(yy,18,birthday)>getdate()