Stored Procedure to get Years,Months,Days bet 2 dates

[dmacasaet]

I am trying to get the results of three integer values corresponding to "number of Years" "number of Months" "number of Days" between two inputted dates...

The code below does not error out but the computed values come out wrong...The expected results should have been Years=0,Months=0 and Days=1...
I am new at MS SQL Server programming. Please help me correct my code. Thanks

****************************************************
CREATE PROCEDURE dbo.HowLongAgo
@d1 smalldatetime ,
@d2 smalldatetime
AS
declare @tempdate smalldatetime, @Years int,@Months int,@Days int
BEGIN
select @tempdate = @d1


select @Years = DateDiff("yyyy", @tempdate, @d2)
select @tempdate = DateAdd("yyyy", @Years, @tempdate)


select @Months = DateDiff("m", @tempdate, @d2)
select @tempdate = DateAdd("m", @Months, @tempdate)

select @Days = DateDiff("d", @tempdate, @d2)
select @tempdate = DateAdd("d", @Days, @tempdate)

update test_table set dateCol_9 = @Years , dateCol_10 = @Months ,dateCol_11 = @Days

END
GO
********************************************

exec HowLongAgo '12/31/97','01/01/98'

select * from test_table

 

[SQLBill]

What results are you getting?

-SQLBill

 

[SQLBill]

I'll bet you are getting:

Years=1,Months=1 and Days=1

Correct?

Here's why:

From Books OnLine (bolding for emphasis):
DATEDIFF: Number of date and time boundries crossed between two specific dates.

So, one year boundry is crossed (from 97 to 98), one month boundry is crossed (from December to January), and one day boundry is crossed (from 31 to 1).

-SQLBill

 

[dmacasaet]

Thank you SQLBill
I get the values Years= 1 , Months= -11 and Days= -30 ...

 

[SQLBill]

Of course, <sound of hand slapping forehead>!

DATEDIFF(yyyy, '12/31/97', '01/01/98')

Means:

98 minus 97 = 1

DATEDIFF(m, '12/31/97', '01/01/98')

Means:

01 minus 12 = -11

DATEDIFF(d, '12/31/97', '01/01/98')

Means:

01 minus 31 = -30

BTW-you don't need single quotes around the type of date (yyyy instead of 'yyyy')

-SQLBill

 

[dmacasaet]

I am actually a cold fusion programmer and rewrote this code from coldfusion (where it works perfectly)... Is there a way I can get my desired results in Transact-SQL? Any help will be very appreciated. Thanks
Thanks again SQLBill

 

[SQLBill]

Let me give it some thought and I'll see what I can come up with. Maybe someone else already has an idea.

-SQLBill

 

[JamesLean]

I've tested this quite throughly and think it works - let me know if you find any holes!

CREATE PROC dbo.HowLongAgo
	@start datetime,
	@end datetime
AS

DECLARE @orig_start datetime,
	@y int,
	@m int,
	@d int

SET @orig_start = @start

--years
SET @y = DATEDIFF(yy, @start, @end)
SET @start = DATEADD(yy, @y, @start)

--months
SET @m = DATEDIFF(mm, @start, @end)
SET @start = DATEADD(mm, @m, @start)

IF @m < 0
BEGIN
	SET @y = @y - 1
	SET @m = @m + 12
END

--days
SET @d = DATEDIFF(dd, @start, @end)

IF @d < 0
BEGIN
	SET @m = @m - 1
	SET @d = @d + CASE DATEPART(mm, @orig_start)
		WHEN 1 THEN 31
		WHEN 2 THEN CASE WHEN DATEPART(yy, @orig_start) % 4 = 0 THEN 29 ELSE 28 END
		WHEN 3 THEN 31
		WHEN 4 THEN 30
		WHEN 5 THEN 31
		WHEN 6 THEN 30
		WHEN 7 THEN 31
		WHEN 8 THEN 31
		WHEN 9 THEN 30
		WHEN 10 THEN 31
		WHEN 11 THEN 30
		WHEN 12 THEN 31
	END
END

SELECT @y AS Years, @m AS Months, @d AS Days
GO

EXEC HowLongAgo '19971231', '19980101'

--James

 

[dmacasaet]

this is excellent excellent james ...other users in this group will find this very useful...again thanks

 

[akabatman2]

Hi James,

I have been searching for hours and this is the closest thing I've found to a solution for what I would like to do.

I would like to be able to find the difference between two dates with the resulting value being the number of months, between the @start and @end Date. The resulting value would be a decimal value for use in calculating the average number of months for a contract term for instance.

Record#1:
@startdate = '1/1/2005'
@enddate = '1/15/2005'

ContractTermMonths = .5

Record#2:

@startdate = '2/1/2005'
@enddate = '4/30/2005'

ContractTermMonths = 3

I was thinking this might be a UDF that would need @DatePart, @StartDate,EndDate. This way the table.startdate and table.enddate fields could be returned in a select statement against a SQL Server table and the average aggregated.

I wish I knew how to code this, I am thinking I would have to be at the DBA level to figure this one out.

Any ideas on a solution?

Any help would be much appreciated.

 

[JamesLean]

I would think the easiest way to get what you want (not absolutely exact but close enough) would be to just divide the number of days difference by 30:

DECLARE @start datetime,
	@end datetime

SET @start = '20050201'
SET @end = '20050430'

SELECT CAST(1.0 * DATEDIFF(dd, @start, @end) / 30 AS decimal(10, 1))

--James

 

[akabatman2]

I'll Try it. Thanks James!

 

[gmmastros]

I have a UDF that is well tested, works for leap years, and is quite accurate.

ALTER   Function dbo.CalculateAge
	(
	@BirthDate As DateTime,
	@Now as DateTime
	)
	Returns VarChar(100)
AS
Begin
--dateformat= yyyymmdd
declare @from datetime 
declare @to datetime 

set @from = Convert(VarChar(4), Year(@BirthDate)) 
			+ Right('00' + Convert(VarChar(2), Month(@BirthDate)), 2)
			+ Right('00' + Convert(VarChar(2), Day(@BirthDate)), 2)

set @to = Convert(VarChar(4), Year(@Now)) 
			+ Right('00' + Convert(VarChar(2), Month(@Now)), 2)
			+ Right('00' + Convert(VarChar(2), Day(@Now)), 2)

declare @year int set @year=0
declare @month int set @month=0
declare @day int set @day=0
declare @rndate datetime     
set @rndate=@from --rndate = runningdate

--calculate the @year 
if(datepart(year,@to)>datepart(year,@from))
begin
    set @year = datediff(year,@from,@to)
    set @rndate = dateadd(year,@year,@from)    --update runningdate
    if(@rndate>@to)
    begin
        set @year = datediff(year,@from,dateadd(year,-1,@to)) --calculate years from @from to @to - 1 year
        set @rndate = dateadd(year,@year,@from)    --update runningdate
    end
    
end

--add 1 month as long as running date is smaller than or equal to @to
while @rndate<=@to
begin
    set @rndate = dateadd(month,1,@rndate)
    if (@rndate<=@to)
    begin 
        set @month=@month+1
    end
end
--set @rndate back 1 month
set @rndate=dateadd(month,-1,@rndate)

--start to count days
while @rndate<@to
begin
    set @rndate=dateadd(day,1,@rndate)
    set @day=@day+1
end

Return Convert(VarChar(5), @year) + ' Years, ' + Convert(VarChar(5), @Month) + ' Months, ' + Convert(VarChar(5), @day) + ' Days'
End

I don't necessarily recommend running this on a LARGE dataset since performance may be a little substandard. On a record by record basis, or even a few thousand records, the performance should be pretty good.

To use this....

Select dbo.CalculateAge('3/23/1970', GetDate())

or...

Select 	FirstName, 
	LastName, 
	Birthdate, 
	dbo.calculateAge(BirthDate, GetDate()) As Age
From 	People
Where 	Birthdate Is Not NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[akabatman2]

Thanks George, I'll be checking this out. I am going to test it with a different date other than GetDate() for the @Now parameter.

This is great, thanks again.