我在sql server2000下面有个数据库表birth,两列
name char(12),
birthday smalldatetime怎么用insert语句插入一条纪录,比如“张三,1980-4-5”
insert into birth
values (‘张三’,1980-4-5)
这么插入后,发现日期完全不对了,要怎么写呢?另外,表中已经有了纪录,怎么得到今天过生日者的年龄?
name char(12),
birthday smalldatetime怎么用insert语句插入一条纪录,比如“张三,1980-4-5”
insert into birth
values (‘张三’,1980-4-5)
这么插入后,发现日期完全不对了,要怎么写呢?另外,表中已经有了纪录,怎么得到今天过生日者的年龄?
select * from birth where datediff(day,birth,getdate())=0
select * from birth where datediff(day,birth,getdate())=0
把引号换成英文状态的
insert birth select ('张三','1980-4-5')
--查找今天过生日的用户的年龄
select *,datediff(yy,birthday,getdate()) from tb where datediff(dd,birthday,getdate())=0
insert birth select ('张三','1980-4-5')
--查找今天过生日的用户的年龄
select *,datediff(yy,birthday,getdate()) from tb
where sutff(convert(varchar(10),birthday,120),1,4,'')=
sutff(convert(varchar(10),getdate(),120),1,4,'')
--用datepart或month,day等函数处理也可以
insert into birth values('张三','1980-4-5')
--插入方法2
insert into birth select '李四','1983-5-6'
--插入方法3
insert into birth(name,birthday) values('张三','1980-10-31')--获取今天过生日的人的年龄 getdate()获得的日期为2007-10-31
select *,year(getdate())-year(birthday) as 年龄 from birth
where month(birthday)=month(getdate())
and day(birthday)=day(getdate())
/*
张三 1980-10-31 00:00:00 27*/
比如:我写
insert into birth
select
'李四','1983-5-6'
'张三','1980-4-5'
似乎不行!
select '李四 ', '1983-5-6 &apos
union
select '张三 ', '1980-4-5 &aposFROM (select top 1 * from syscolumns) a
年龄:本日减去数据库中的日期
select * from birth where datediff(day,birth,getdate())=0
create table birth(
name char(12),
birthday smalldatetime
)
go
insert into birth values('张三','1981-4-5')
insert into birth values('李四','1981-11-1')select *,datediff(yy,birthday,getdate()) as age from birth
where datepart(mm,birthday)-datepart(mm,getdate())=0 and datepart(dd,birthday)-datepart(dd,getdate())=0drop table birth/****
name birthday age
------------ ------------------------------------------------------ -----------
李四 1981-11-01 00:00:00 26
****/
name char(12),
birthday smalldatetime
)
go
insert into birth values('张三','1981-4-5')
insert into birth values('李四','1981-11-1')go
select
*
from
birth
where
datediff(d,dateadd(yy,datediff(yy,birthday,getdate()),birthday),getdate())=0name birthday
------------ ------------------------------------------------------
李四 1981-11-01 00:00:00(所影响的行数为 1 行)
--查当月
select
*
from
birth
where
datediff(m,dateadd(yy,datediff(yy,birthday,getdate()),birthday),getdate())=0
insert into @tb
select '张三','1981-4-5' union
select '李四','1981-11-1'select name1,birthday,datediff(yy,birthday,getdate())as age from @tb
where datepart(mm,birthday)-datepart(mm,getdate()) = 0
and datepart(dd,birthday)- datepart(dd,getdate()) = 0
(name char(8),
birthday smalldatetime
);
insert into birth values('张三','1980-4-5')
这样插入就可以了啊
http://www.cnblogs.com/Microshaoft/archive/2005/04/26/145334.html
create function udf_GetAge(@StartDate datetime,@EndDate datetime)
returns integer
-- 返回精确年龄 select dbo.udf_GetAge('1949-10-01',getdate())
begin
return datediff(year,@StartDate,@EndDate)
- case when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0
then 0
else
1
end
end
month(birth)=month(getdate()) and day(birth)=day(getdate())