select datediff(yy,出生日期,getdate()) as [年龄] from tb
SELECT 年龄=case when datediff(day,dateadd(year,datediff(year,'1982-07-18',getdate()),'1982-07-18'),getdate())>=0 then datediff(year,'1982-07-18',getdate())else datediff(YY,'1982-07-18',getdate())-1 end
/* 标题:一个项目涉及到的50个Sql语句(整理版) 作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开 */--46.1 只按照年份来算 select * , datediff(yy , sage , getdate()) [年龄] from student --46.2 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一 select * , case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1 else datediff(yy , sage , getdate()) end [年龄] from student
--借宝地练习练习 declare @birth varchar(60) set @birth=(select 最后登录时间 from 登录表 where 员工编号='administrator') print @birth select datediff([year], cast(@birth as datetime),getdate()) as age--结果为0
--借宝地练习练习 declare @birth varchar(60) set @birth=(select 最后登录时间 from 登录表 where 员工编号='administrator') print @birth select datediff([year], cast(@birth as datetime),getdate()) as age--结果为0
declare @date datetime='1986-07-21' select case when (MONTH(@date)>MONTH(GETDATE())) or (MONTH(@date)=MONTH(GETDATE()) and DAY(@date)>DAY(GETDATE())) then DATEDIFF(YY,@date,GETDATE())-1 when (MONTH(@date)<MONTH(GETDATE())) or (MONTH(@date)=MONTH(GETDATE()) and DAY(@date)<=DAY(GETDATE())) then DATEDIFF(YY,@date,GETDATE()) end as '年龄'
select datediff([year],'1984-09-09',getdate())
/*
27
*/
--把固定改成出生日期字段,即可粗略计算年龄
select datediff(yy,出生日期,getdate()) as [年龄]
from tb
年龄=case when datediff(day,dateadd(year,datediff(year,'1982-07-18',getdate()),'1982-07-18'),getdate())>=0
then datediff(year,'1982-07-18',getdate())else datediff(YY,'1982-07-18',getdate())-1 end
标题:一个项目涉及到的50个Sql语句(整理版)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开
*/--46.1 只按照年份来算
select * , datediff(yy , sage , getdate()) [年龄] from student
--46.2 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
select * , case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1 else datediff(yy , sage , getdate()) end [年龄] from student
--借宝地练习练习
declare @birth varchar(60)
set @birth=(select 最后登录时间 from 登录表 where 员工编号='administrator')
print @birth
select datediff([year], cast(@birth as datetime),getdate()) as age--结果为0
--借宝地练习练习
declare @birth varchar(60)
set @birth=(select 最后登录时间 from 登录表 where 员工编号='administrator')
print @birth
select datediff([year], cast(@birth as datetime),getdate()) as age--结果为0
declare @date datetime='1986-07-21'
select case when (MONTH(@date)>MONTH(GETDATE())) or (MONTH(@date)=MONTH(GETDATE()) and DAY(@date)>DAY(GETDATE()))
then DATEDIFF(YY,@date,GETDATE())-1
when (MONTH(@date)<MONTH(GETDATE())) or (MONTH(@date)=MONTH(GETDATE()) and DAY(@date)<=DAY(GETDATE()))
then DATEDIFF(YY,@date,GETDATE())
end as '年龄'