I need help. We are trying to calculate the difference between 2 dates. Using DateDiff, and yyyy only gives us the difference in years. We may and will have the situation where '03/20/2003' and '03/24/2006' is greater than 3 years. By using DateDiff with the 'yyyy' option, we are not greater than 3 years.
Is there a way to calcuate the difference in 'mm/dd/yyyy' and get a correct solution?

Thanks in Advance
Len

No exactly sure what you're trying to do. But here's a way to do what I'm not sure you're trying to do.

Declare @years varchar(5), @months varchar(5), @days varchar(5), @thedatediff varchar(15)

Set @years = DateDiff(year,'03/20/2003','03/24/2006')
Set @months = DateDiff(month, '03/20/2003','03/24/2006') % 12
Set @days = DateDiff(day, '03/20/2003','03/24/2006') % 365 -1

Select years = @years, months = @months, days = @days

What exactly would be the correct to solution to the particular problem you stated? '00/04/0000' ??? as you say 'mm/dd/yyyy'

??

Richard

Well, actually you're wrong. The dates you provided is not GREATER than 3 years. It is 3 years and some days.

What you need is GREATER than OR EQUAL to 3 years.

use >=

select convert(int, datediff(dd, '03/20/2003', '03/24/2006')-dateadd(dd, 0, '1903-01-01'))

when the two dates are the same, the result is 1. if you want the result to be 0 when the two dates are the same, change 0 to 1 in the dateadd function.

the result is the number of days exceeding three years, including scot years.

You may just want to use the date add function, adding three years to one date and check if the other data is greater than the adjusted date.

Scourge,

Are you still there? Did you dissappear?

Peso and robprell. Thanks for your replies.

Richard
SQL Search & Rescue

we have hijacked the thread, lol.

Cool... date calculations always fascinated (and troubled) me!

How many days till the end of time? Well... OK... how many pay day Fridays?

declare @m int, @y int , @d int , @dat datetime

set @dat = '2004-12-20'
select @y=datediff(year,@dat,getdate())
select @m=datediff(month,@dat,getdate())

set @m=@m-(@y*12)
if @m < 0
    begin
    set @y=@y-1
    set @m=12+@m
    end

--getting days
set @dat = dateadd(year, @y, @dat)
set @dat = dateadd(month, @m, @dat)
set @d = datediff(day,@dat,getdate())

if @d < 0
    begin
    set @m=@m-1
    set @d=30+@d
    end

if @m < 0
    begin
    set @y=@y-1
    set @m=12+@m
    end

select @y 'year' , @m 'month', @d 'day'
---------------------------------------------
this will work not exactly accurate but nearly (difference can be in +/- 1 day)

Liked robprell's solution. Sometimes you can't see the tree for the forest.

IF DATEADD(yyyy, 3, '03/25/2003') >= '03/24/2006'
   PRINT 'More than three years'
ELSE
   PRINT 'Have not reached three years'