求相隔年数???? 急!!!!!!!!!(很简单)

Question

用DataDiffSELECT DATEDIFF(year,'2001/04/19','2002/04/30')

Answer 1

SELECT DATEDIFF(year,'2001/04/19','2002/04/30')
这个不能得到我预期的效果

Answer 2

SELECT DATEDIFF(year,'2001/04/19','2002/04/20')
---------------- 结果为1  SELECT DATEDIFF(year,'2001/04/29','2002/04/20')
---------------- 结果为1    我想为0
SELECT DATEDIFF(year,'2001/04/21','2002/04/20')
---------------- 结果为1    我想为1
SELECT DATEDIFF(year,'2001/12/29','2002/04/20')
---------------- 结果为1  我想为0

Answer 3

SELECT CASE WHEN  DATEDIFF(day,'2001/04/21','2002/04/20') <365 then 0 else DATEDIFF(year,'2001/04/21','2002/04/20')  end

Answer 4

SELECT CASE WHEN  DATEDIFF(day,'2001/04/21','2002/04/20') <365 then 0 else DATEDIFF(year,'2001/04/21','2002/04/20')  end

Answer 5

declare @dat datetime
set @dat='01-4-30'
select case left(cast((datediff(yy,@dat,getdate())) as varchar(4)),1)  
    when '0' then (case left(cast(datediff(dd,@dat,getdate()) as varchar(4)),1) when '-' 
  then 0 else 1 end)
    when '-' then abs(datediff(yy,@dat,getdate()))
    else datediff(yy,@dat,getdate())
   end    

Answer 6

你的意思是大于现在时间的都为0是吧，那when '-',的时候还让它是0就行了

Answer 7

假定只有一种情况才会让结果等于1
SELECT DATEDIFF(year,'2001/04/21','2002/04/20')
---------------- 结果为1    我想为1
declare @dat datetime
set @dat='2001-4-30'
select case when 
  dateadd(day,-1,dateadd(year,@dat,Datediff(year,@date,Getdate()))=convert(varchar(10),getdate(),21) then 1 else 0 end
如果是求满足条件的相隔年份
declare @dat datetime
set @dat='2001-4-30'
select case when 
  dateadd(day,-1,dateadd(year,@dat,Datediff(year,@date,Getdate()))=convert(varchar(10),getdate(),21) then Datediff(year,@date,Getdate()) else 0 end

Answer 8

假定你要的结果是SELECT DATEDIFF(year,'2001/04/21','2002/04/20')
---------------- 结果为1    我想为1
SELECT DATEDIFF(year,'2000/04/21','2002/04/20')
---------------- 结果为2    我想为1
declare @dat datetime
set @dat='2001-4-30'
select case when 
  dateadd(day,-1,dateadd(year,Datediff(year,@dat,Getdate()),@dat))
=convert(varchar(10),getdate(),21) then 1 else 0 end
假定
SELECT DATEDIFF(year,'2000/04/21','2002/04/20')
---------------- 结果为2    我想为2
SELECT DATEDIFF(year,'2001/04/20','2002/04/20')
---------------- 结果为1    我想为0
SELECT DATEDIFF(year,'2000/04/20','2002/04/21')
---------------- 结果为2    我想为0declare @dat datetime
set @dat='2001-4-30'
select case when 
  dateadd(day,-1,dateadd(year,Datediff(year,@dat,Getdate()),@dat))
=convert(varchar(10),getdate(),21) then Datediff(year,@dat,Getdate()) else 0 end

Answer 9

帮忙看看
http://www.csdn.net/expert/topic/672/672817.xml?temp=.9215509